help

[Reggie2004]

This is the code.
SELECT [Why not workable].[SSN P ], [Why not workable].TXPD, [Why not workable].[LFRZ-RFRZ], [Why not workable].[TC-150], [Why not workable].[TC-290], [Why not workable].[TC-291], [Why not workable].[TC-300], [Why not workable].[TC-301], [Why not workable].[TC-420], [Why not workable].[TC-421], [Why not workable].[TC-424], [Why not workable].[TC-530], [Why not workable].[TC-540], [Why not workable].[TC-590], [Why not workable].[TC-591], [Why not workable].[TC-594], [Why not workable].[TC-599], [Why not workable].[TC-976], [Why not workable].[TC-977], [Why not workable].[ DOB ], [Why not workable].[ DOD ]
FROM [Why not workable]
WHERE [TXPD] <> (SELECT MIN(TXPD) FROM [Why not workable])
GROUP BY [SSN P]
HAVING [SSN P] > 1

The field SSN P has multiple years. I am trying to list all the years except the first. Only when there are multiple years. I get this error message "You tried to execute a query that does not include the specified expression 'TXPD' as part of an aggregate function."

TXPD is my tax year. Please help

 

[dbomrrsm]

SELECT [Why not workable].[SSN P       ], [Why not workable].TXPD, [Why not workable].[LFRZ-RFRZ], [Why not workable].[TC-150], [Why not workable].[TC-290], [Why not workable].[TC-291], [Why not workable].[TC-300], [Why not workable].[TC-301], [Why not workable].[TC-420], [Why not workable].[TC-421], [Why not workable].[TC-424], [Why not workable].[TC-530], [Why not workable].[TC-540], [Why not workable].[TC-590], [Why not workable].[TC-591], [Why not workable].[TC-594], [Why not workable].[TC-599], [Why not workable].[TC-976], [Why not workable].[TC-977], [Why not workable].[  DOB     ], [Why not workable].[ DOD      ]
FROM [Why not workable]

WHERE [TXPD]  > (SELECT MIN(TXPD) FROM [Why not workable])
AND [SSN P] > (SELECT MIN([SSN P]) FROM [Why not workable])
GROUP BY [SSN P]
HAVING COUNT (DISTINCT ([SSN P])) > 1

no time to test but give this a go.

[blue]DBomrrsm[/blue]

 

[donutman]

Doesn't sound like a SQL Server error message, are you in the right forum?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

or try removing the DISTINCT from the having clause

[blue]DBomrrsm[/blue]

 

[SQLSister]

When you use group by, then every field in the select must either be inthe group by or part of an aggregate function like sum(), Max(0, Count(), etc.

Questions about posting. See faq183-874

 

[Reggie2004]

Undefined function 'DISTINCT' IN EXPRESSION IS THE NEW ERROR MESSAGE