Most Recent Date Query

[Walter Steadman]

I have a list of hours flown by different pilots, and what I need is to do a
query that shows me the last time a pilot flew. Such as below:

A1234 3/2/2005
A1234 3/30/2005
A1234 5/4/2005
A1234 10/10/2004
B2345 2/20/2005
B2345 2/27/2005
B2345 3/9/2005
B2345 3/23/2005
B3456 2/20/2005
B3456 3/16/2005
B3456 4/6/2005
B3456 4/14/2005
B3456 4/22/2005
D4567 2/21/2005
D4567 3/31/2005
D4567 4/25/2005
D4567 5/26/2005
D4567 1/13/2005
E5678 2/23/2005
E5678 3/20/2005
E5678 4/20/2005

I would like the query or report to look something like below:

A1234 3/2/2005
B2345 3/23/2005
B3456 4/22/2005
D4567 5/26/2005
E5678 4/20/2005

I have used the SELECT TOP 1 but that only gives me the most recent date of
all. Not sure how to set this up. Have read previous posts, but again I
find myself only getting the top 1 overall

Thanks in Advance

Wally Steadman
US Army in Iraq

 

[Ken Snell [MVP]]

You use a subquery to get the most recent date for each of the pilots and
then use that to return the appropriate records:

SELECT T.PilotNum, T.FlyDate
FROM YourTableName AS T
WHERE T.FlyDate =
(SELECT Max(Q.FlyDate) AS QFD
FROM YourTableName AS Q
WHERE Q.PilotNum = T.PilotNum);

 

[Jeff Boyce]

Wally

GroupBy A_ID, Max(FlightDate)

Use a "Totals" query with the above.

Jeff Boyce
<Access MVP>