MS Access Select the most recent date for each unique Id

Question

I am working with MS Access Database. I have following table:

Product Id      Date Of Purchase
    A              01-01-2008
    B              01-01-2013
    C              01-01-2012
    A              01-01-2014
    B              01-01-2007
    C              01-01-2011

I need to find records for the latest bought products (for all Products)

i.e Desired Output should be:

Product Id      Date Of Purchase
    A              01-01-2014
    B              01-01-2013
    C              01-01-2012

I am relatively new to databases. Please help me with the query. Thanks.

Solution

Try using the Max function whilst grouping by Product Id

SELECT Product ID, MAX(Date Of Purchase)
FROM [table]
GROUP BY Product ID

Comments

[HardBoiled]

Thanks. It works. Also want to know if I have another column like ProductName.. How can I fetch that also.. When I try to include it it says error. Error: You tried to execute a query which doesnt have MAX function on ProductName.

[DMK]

When grouping any colums within the select must be part of the Group by clause or within an aggregate function such as Max. I'm assuming Product name is unique per Product Id therefore you could do SELECT Product ID, MAX(Product name), MAX(Date Of Purchase) or alternativley you could add it to the group by clause GROUP BY Product ID, Product name