MS Access - INNER Join Group By Max - Without Inner Join?

[ironfelix717]

Hi all,

Basically, I am querying data which has a record version field. The max verison number is the latest record. I need to display all data in the table associated with the latest version.

I've been able to do this with the help of this link: https://www.techonthenet.com/access/...max_query2.php



This method requires a "double query", if you will. It needs an intermediate query that is joined to the final query.

I was wondering if there is any way to eliminate the need for this double query, where I can query this data with one single SQL statement.

I've attached an example where you can see the data and results. JoinMaxQuery.accdb.zip


Thanks for your help!

[pbaldy]

The first query can be a subquery:

SELECT...
FROM TableName INNER JOIN (SELECT...) ON...

but personally I find them difficult to work with that way, at least in Access. The design grid can do funky things to subqueries.

[June7]

You can nest queries. Copy/paste the first query statement into the second. Use SQLView of query builder:

SELECT Table1.ID, Table1.Version, Table1.Val, Table1.Val2
FROM Table1 INNER JOIN (SELECT Table1.ID, Max(Table1.Version) AS MaxOfVersion
FROM Table1 GROUP BY Table1.ID) AS Query1 ON (Table1.Version = Query1.MaxOfVersion) AND (Table1.ID = Query1.ID);

Here's another query approach:

SELECT * FROM Table1 WHERE Record IN (SELECT TOP 1 Dupe.Record FROM Table1 AS Dupe WHERE Dupe.ID=Table1.ID ORDER BY Dupe.Version DESC);

Review: http://allenbrowne.com/subquery-01.html#TopN

[ironfelix717]

June7 & pbaldy,

Those solutions will work and work nicely! Especially the Allen Browne approach. Great, this makes my project much cleaner!

Thank you so much.

[ironfelix717]

June7,

Is there any way I can choose which fields I want to return from the Allen Browne query?

If I ONLY want ID, Val1, and Val2 fields to return in the query, how might I still use his approach?

Thanks

[June7]

Yes, you can explicitly reference field names instead of * wildcard. His example shows that. I just went the lazy route.

[ironfelix717]

Silly me, I missed the asterisk somehow! Thanks a bunch!