The Problem (Q-score 5, ranked #55th of 67 in the Access VBA archive)
The scenario as originally posted in 2009
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!
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+6)
6-line Access VBA pattern (copy-ready)
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
When to Use It — vintage (14+ years old, pre-2013)
Ranked #55th in its category — specialized fit
This pattern sits in the 81% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.
What changed between 2009 and 2026
The answer is 17 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.