How to Select Most recently updated Records in Access for each Person

August 24, 2006

Well. First Disclaimer. I am not very good at writing queries. So it might be an easy task for someone, but it wasn’t for me.

The problem was simple but I spent a considerable amount of time before I got the solution.

I have a table in Access called Designation_Person. It contains Person_ID, Designation_ID and Date_Updated. Now a person can have several designations over time and these are distinguished by Date_Updated.

e.g Records can be of this form 

  1. Person_ID = 1, Designation_ID = 100, Date_Updated = April 5, 2006
  2. Person_ID = 2, Designation_ID = 120, Date_Updated = May 9, 2005
  3. Person_ID = 1, Designation_ID = 150, Date_Updated = August 25, 2006

Now the problem was that I wanted to have only the CURRENT designation of a person i.e the record with the latest Date_Updated for each Person_ID.

This is how I achieved it

SELECT Designation_Person.*
FROM Designation_Person
WHERE (((Designation_Person.Date_Updated)=(SELECT MAX(Date_Updated)
FROM Designation_Person AS date_max
WHERE date_max.Person_ID = Designation_Person.Person_ID)));


Just posting this if someone else is trying to do similar thing, he would have to waste lesser time 🙂