How do I restrict a query to give only one name&date per name?

[Guest]

My query has three fields - name,date,notes. Each name will have several
dates. I want to select only the latest date for each name, so that I get a
report which gives me the latest notes for each name. I cannot work it out.
Any help would be appreciated.

 

[Guest]

Create a temp query with just the name and the date as columns. Click on the
totals button and leave Name as Group By but change Date to Max.
Save this query. Open the query you have already created and add the temp
query from above. Create joins on the Name and Date and then run this. You
should then get the notes for the latest date for each person.

 

[John Spencer (MVP)]

SELECT [Name],[Date],[Notes]
FROM YourTable
WHERE YourTable.[Date] = (SELECT MAX(T.[Date]) FROM YourTable as T WHERE
T.[Name] = YourTable.[Name])

By the way, Name and Date are not good field names. Every object in Access has
a NAME property and Date is a function to return the current system date. You
would be better off renaming those fields to PersonName and NoteDate.