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
- Person_ID = 1, Designation_ID = 100, Date_Updated = April 5, 2006
- Person_ID = 2, Designation_ID = 120, Date_Updated = May 9, 2005
- 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
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 🙂