Access request to select rows that are MAXS of one column, but unique on another

Question

Suppose I need a table that gives me 4 columns of data below, but I want only one row for each unique "identifier", using the "Date" column to select only the most recent date for each unique identifier ":

|--ID--|-Type-|-Code-|--Date--| | 1 | A | 11 |11/07/13| | 2 | A | 11 |11/07/13| | 2 | B | 12 |10/07/13| <-- don't want this record from ID=2 as | 3 | A | 11 |11/07/13| there is a more recent date for it | 4 | A | 10 |11/07/13| than 10/07/2013 | 5 | A | 11 |11/07/13| 

I tried to adapt this answer , which seemed to be for a similar question, but I get the following error:

Your request does not include the specified expression 'ID' as part of the aggregate function part

Here is my adapted version of this SQL that I tried to run in Access:

 SELECT ESM.ID, ESM.Type, ESM.Code, ESM.Date FROM Emp_Stat_Mon As ESM INNER JOIN ( SELECT ID, MAX(Date) AS MaxDate FROM Emp_Stat_Mon GROUP BY ID ) groupedESM ON ESM.ID = groupedESM.ID AND ESM.Date = groupedESM.MaxDate; 

Answer 1

It does:

 SELECT ID, Type, Code, Date FROM Emp_Stat_Mon t1 WHERE t1.Date = (SELECT Max(t2.Date) FROM Emp_Stat_Mon t2 WHERE t2.ID=t1.ID GROUP BY t2.ID) 

SQLFiddle with your sample data: SQLFiddle

Answer 2

I just needed to work the same way. I used DMax() to get me there, but essentially does the same thing as the Ron code.

 SELECT ID, Type, Code, Date FROM Emp_Stat_Mon As ESM WHERE ESM.Date = DMax("Date", "Emp_Stat_Mon", "ID=" & ESM.ID); 

EDIT:

Since I put this together, I was concerned about performance. I expect my own table to become large, so this method should probably be avoided. I thought there was a good discussion here . I was not able to get the second SELECT to work for me, but I think it deserves a second chance!