Access SQL query: find the row with the most recent date for each distinct entry in a table

calendar_today Asked Nov 9, 2009
thumb_up 6 upvotes
history Updated April 16, 2026

Question posted 2009 · +5 upvotes

All,

I’m sure this is a pretty simple SQL query question, but I’m sure there’s a good way, and a very BAD way, to do this. Left to my own devices, I’m liable to end up with the latter. So…

I have a table in Access with data that looks like this:

ID      Value  As_of
1173    156    20090601
1173    173    20081201
1173    307    20080901
1173    305    20080601
127     209    20090301
127     103    20081201
127     113    20080901
127     113    20080601
1271    166    20090201
1271    172    20081201
1271    170    20080901
1271    180    20080601
...

What I’d like to get is the “Value” for each unique ID with the most recent “As Of” date (which is in YYYYMM format).

So, my result set should look like this:

ID      Value    As_of
1173    156      20090601
127     209      20090301
1271    166      20090201

Note that different IDs will have different “As Of” dates. In other words, I can’t simply indentify the most recent as of globally, then select every row with that date.

For what it’s worth, this table has about 200,000 total rows, and about 10,000 unique IDs.

Many thanks in advance!

Accepted answer +6 upvotes

If you need both the date and the value, you need to do a join:

SELECT ID, Value,As_of 
from yourTable a inner join 
          (SELECT ID, MAX(As_of) as As_of 
          from yourTable group by ID) b 
on a.ID=b.ID and a.As_of = b.As_of

Top ms-access Q&A (6)

+6 upvotes ranks this answer #46 out of 55 ms-access solutions on this site .
ms-access