Where clauses and aggregate functions.....

[KellyJo]

Can anyone tell me how to fix this code so that it works? You can't have an aggregate function in a where clause.

s = "SELECT [ID] as PK, ERName AS ERName FROM [Staffing Data] WHERE [ID] = max([ID])"

Thanks!

KellyJo

 

[RoyVidar]

That's not code, that's SQL, there is a forum for queries and sql (forum701)

[tt]...where id=(select max(id) from [staffing data])[/tt]

or

[tt]select top 1 id as pk <rest of statement> order by id desc[/tt]

Roy-Vidar

 

[lupins46]

s = "SELECT [ID] as PK, ERName AS ERName FROM [Staffing Data] WHERE [ID] = dmax("[ID]","Staffing Data")