Need query to return fields with most recent date

[fgbdrum]

Hello,

Have a table and many of the fields in the table have duplicate information
in every column, except for date. I need a query to return only those fields
with the most recent date in it. I apologize for the seemingly basic
question, but haven't been able to figure this out. Thank you.

 

[Jerry Whittle]

The first thing to try would be creating a new query with all the fields that
yo wish to see. Then turn it into a Totals query. How you do this depends on
the version of Access. See Help and remember to post the version in the
future. Group By all the duplicate fields and Max on the date field. Note:
This depends on the date field being an actually date/time data type. Also
avoid using First or Last as they often don't work as expected.

If that doesn't work, you may need a subquery. If so post some example data,
the expected results, and the SQL for the query that didn't work.

 

[fgbdrum]

I was incorrect, not all of the data is duplicated in the other fields. I am
using 2003 version. Here's what my table looks like:

Employee ID Name Rank Date
123456 John Smith 5 10/1/2005
123456 John Smith 4 9/1/2009

I want my query to return the field for 9/1/2009 with the Ranking of 4. I do
not need to see the other field of data. I tried to to Max on date and Min on
Ranking but that won't get me what I need because if the person's ranking
goes up on a later date, (ex: if the ranking goes to 6 from a 5 on 9/1/09),
then it will give me the lower of the two ranks (5) and the later of the two
dates (9/1/09) which is not what I want.

Thank you.

 

[Jerry Whittle]

SELECT T1.[Employee ID],
T1.[Name],
T1.[Rank],
T1.[Date]
FROM Fgbdrum as T1
WHERE T1.[Date] In (SELECT TOP 1 T2.[Date]
FROM fgbdrum AS T2
WHERE T2.[Employee ID]= T1.[Employee ID]
ORDER BY T2.[Date] DESC);

You need to replace both Fgbdrum's with the proper table name.

Speaking of names, both Name and Date are reserved words. This could cause
trouble if you don't remember to put square brackets [ ] around them. For
more about reserved words, check out:
http://support.microsoft.com/kb/286335/