---

MS Access 2003: Query to retrieve the most recent record from a table

This MSAccess tutorial explains how to create a query to retrieve the most recent record from a table in Access 2003 (with screenshots and step-by-step instructions).

Question: I have an Access 2003/XP/2000/97 database that contains Contact records with their associated History. I'm trying to run a SQL query to retrieve the most recent record only (disregard all older records) for each Contact. The problem is that the query keeps returning ALL of the history records, not just the most recent.

I've tried the following SQL statement and it doesn't seem to work:

SELECT (select max(History.HistoryID) from History where History.RefID = Contacts.ID),
Contacts.ID, Contacts.FirstName, Contacts.MiddleName, Contacts.LastName, Contacts.Company, Contacts.Email, History.Method, History.ContactedBy, History.Comments, History.RefID, History.LastContact, History.NextContact
FROM History LEFT OUTER JOIN Contacts
ON History.RefID = Contacts.ID
WHERE LastContact <= 8/28/2003
ORDER BY LastContact;

Answer: Since you are trying to find the most recent record in the History table for each contact, you will need to create two queries in Access. (It can not be done in a single query since you are also trying to retrieve other informational fields in your result set.)

The first query will determine the most recent record, while the second query returns all of the fields that you wish to view in your result set.

We've created an Access database that you can download to view these queries.

Download version in Access 2000

Let's take a closer look at our example.

Our first query is called "Step 1 - Find most recent record for each Contact". This query finds the most recent LastContact date for each Contact where the LastContact is less than or equal to Aug 28, 2003.

The second query is called "Step 2 - Return final results". This query joins the Contacts and History tables with the results from the first query. This allows us to retrieve the necessary fields for our result set.