Selecting Most Recent Date in a Query 2

[UberZoot]

Ladies and Gents,
I have a Table that tracks 20-50 individuals' performance reports over time (quarterly). How can I have a query pull up the most recent evaluations (date) of each individual form this history?

Thank you in advance,

JB

 

[Golom]

Generically ...
[tt]
Select PersonID, TheDate, ... other fields

From tbl T

Where TheDate =

(Select MAX(TheDate) From tbl X
Where X.PersonID = T.PersonID)
[/tt]

 

[UberZoot]

Golom,
Is there any way to tag this under the Criteria section in the design view. I've attached a few other tbls under this qry and I'm still a beginner w/the SQL. Thanks.

JB

 

[dzepeda]

This is not a criteria, you must show the totals row (menu View ->Totals), under your field will appear Group By , then select Max as Total (you also can select min, averange, count, etc)

Greetings from Chile

 

[Golom]

I don't much use design view but I believe that if you copy the sub-query and paste it on the "Criteria" line for the "TheDate" (or whatever your date field is), that should produce the above SQL.

dzepeda

Yes it is a criteria in the sense that it appears in the WHERE clause and serves to select specific records for the output set. Also, there should be no need to use Group By. This construct is designed to select unique records for each PersonID (i.e. the record where the MAX date occurs). You will need groups only if there are multiple records that have exactly the same MAX date.

 

[UberZoot]

Thanks Golom. You're always hooking me up.

JB