Select record in Access with most recent date

Question

I have an Access table.

ID  Field1  Field2  Date
1   un_1    x       201701
2   un_2    y       201704
3   un_1    z       201702
4   un_3    a       201703
5   un_2    b       201709

I would like to take the unique (for Field1) records of this table where Date is the most recent.

I tried:

SELECT ID, Field1, Field2, Date
FROM MYTABLE
WHERE Date=SELECT(MAX(MYTABLE.Date) FROM MYTABLE WHERE ID=MYTABLE.ID)
GROUP BY Field1;

But it is not working.

As result I would expect:

un_1    z       201702
un_2    b       201709
un_3    a       201703

Erik A Erik A · Accepted Answer · 2017-10-05T09:04:44

Your syntax is wrong. You need to put the parentheses around the subquery, like this, and you need to add a synonym for your table, because you're using the same table twice:

SELECT ID, Field1, Field2, Date
FROM MYTABLE i
WHERE MYTABLE.[Date]=(SELECT MAX(t.[Date]) FROM MYTABLE t WHERE t.ID=i.ID)
ORDER BY Field1;

Select record in Access with most recent date

1 Answers