MS Access - Find latest date


Todd Anderson

I have a table which in simple terms has multiple items
that have multiple events associated with multiple dates,
looks something like this:

Item 1 Date1 Event1
Item 1 Date2 Event2
Item 1 Date3 Event3
Item 2 Date2 Event4
Item 2 Date4 Event2
Item 3 Date5 Event1

What I want to do is report on (hopefully through a
query?) the current status of each item, so I need to be
able to say for "Item 1, what is the latest event entry,
by date", "For Item 2, what is the latest event", etc..
So the resultant query or report for the example table
should look like this (assuming Date1 is earliest date,
and Date5 is latest date):
Item1 Date3 Event3
Item2 Date4 Event2
Item3 Date5 Event1

Can someone help?

John Spencer (MVP)

I think that something like the following should work.

SELECT ITEM, Date, Event
FROM Table
WHERE Date =
(SELECT Max(T.Date)
FROM Table As T
WHERE T.Item = Table.Item)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Latest Dates 2
Update Query problem 5
If/Then/Else Query 6
Count Based on Date 8
Value in Crosstab Query 1
How to create a report like... 1
Multicast delegates and hashtable problem 10
query multiple fields 1