MS ACCESS QUERY TO FIND LATEST RECORD VERSION

[davsanto]

I have a list of records such as:

RCD# ID# NAME VERSION
1 1.1 XYZ 1
2 1.2 ABC 1
3 1.3 DEF 1
4 1.1 XYZ 2

Records 1 and 4 are similar except record 4 contains changes that are reflected in the second version. I want to keep the first version for record 1 for reference and historical reasons. The query I want to construct would display all the latest records for both versions 1 and 2 but only the record ID for version 2 so the list would look like this:

RCD# ID# NAME VERSION

2 1.2 ABC 1
3 1.3 DEF 1
4 1.1 XYZ 2

This displays all the records with the latest version. Do I need to create 3 queries, one query to get version 1 records the second query to get version 2 records then the third query to query the first and second queries to get it to compare the ID#? Thanks in advance.

 

[cmmrfrds]

If your records and version are numbered sequentially this should work.

Select max(RCD#), ID#, NAME, Max(VERSION)
From yourtable
Group By ID#, NAME

 

[davsanto]

Yes that works but I think I did not explain myself very well. The results I want to see is all versions of the records except the latest version of records that have the same RCD# - note this is not a unique #.

Thanks

 

[cmmrfrds]

Try using a correlated subquery, or a derived table would also work.


Select A.RCD#, A.ID#, A.NAME, A.VERSION
From yourtable A
Where RCD# NOT IN (Select max(RCD#) from yourtable B
Where B.ID# = A.ID#
Group By ID#)