Group By Query

[Chris Morris]

I have a table which lists people's passport records - we'd like to keep them all as a historical record, hence the one to many. I would now like a query which finds the last / most recent passport and its associated details.

I can perform a Group By query and single out a Max(Expiry_Date), but I also want the Passport number and other information relating to that passport record. If I also include a Max(Issue_Number) then it brings back (unsurprisingly) the highest issue number, which is unrelated to the same passport record as the Max(Expiry_Date). Issue_Number is not an autonumber, but alphanumeric information from the passport issuing authority.

Here's an example of the table data (filtered just on one contact):

Contact_ID | Expiry_Date | Issue_Number
14133 | 20/07/1999 | 5798989
14133 | 10/05/2004 | UK0009983
14133 | 04/02/2009 | UK0018251
14133 | 17/04/2019 | M06951186

Doing a Max() on both the Expiry_Date and Issue_Number brings back:

Contact_ID | Expiry_Date | Issue_Number
14133 | 17/04/2019 | UK0018251

So, I'd like the Expiry_Date to "drive" the selection of the record, and then all other information to fall into line.

Appreciate your help in advance.

 

[CJ_London]

I would use a sub query along the following lines:

Select * From tblPassport where ExpiryDate =(SELECT Max(ExpiryDate) from tblPassport as tmp WHERE ContactID=tblPassprt.ContactID)

This will also result in an updateable query which may or may not be important to you

 

[Chris Morris]

Thank you CJ_London, that was right on the money! Looks like I need to bone up on sub-queries...

 

[CJ_London]

I use them all the time!