Help with this query in Access

Question

ID-     DATE-       NAME
10100-  2010/04/17- Name1
10100-  2010/04/14- Name2
10200-  2010/04/17- Name3
10200-  2010/04/16- Name4
10200-  2010/04/15- Name5
10400-  2010/04/01- Name6

I have this fields(and others too) in one table. I need to do a query which return the ID with your respective name where more recently date for example the results for desired query in that data example will be.

10100-  2010/04/17- Name1
10200-  2010/04/17- Name3
10400-  2010/04/01- Name6

Ommiting ID with older dates.

Then I need one query for that. thanks.

Answer 1

SELECT ID, Date, Name
FROM myTable
INNER JOIN (SELECT ID, Max(Date) AS MaxDate FROM myTable GROUP BY ID) myTable2
ON myTable.ID = myTable2.ID 
WHERE myTable.Date = myTable2.MaxDate

Note: this is written without trying it out. So, please be kind :)

Answer 2

Rather try something like

SELECT table1.*
FROM Table1 INNER JOIN
(
SELECT Table1.ID,  Max(Table1.Date) AS MaxOfDate
FROM Table1
GROUP BY Table1.ID) sub ON Table1.ID = sub.ID and Table1.Date = sub.MaxOfDate

You also might want to change the column names, if this is not just an example, as these are reserved words.