Show MAX Usage and Date of Maximum Usage (1 Viewer)

I have a product table (tblProducts) and a usage table (tblUsage).

tblProducts
- ProductID (PK)
- Product (Text)

tblUsage
- UsageID (PK)
- ProductID (FK)
- UsageDate (Date)
- UsageAmount (Number)

I would like to have a query that shows me what the Maximum usage for all products in a given year AND also the date of the returned usage.

So I have a query that has this


I have also set the criteria for YEAR as 2002 (via a textbox in a form).

This works fine to show me the maximum usage for each individual product in 2002, but how do I show the UsageDate for MaxOfUsage??

What happens if there are two (or more) UsageDate(s)/with the same UsageAmount = MaxOfUsage?

This is the answer I posted a couple of days ago:

You have to do this in two parts. One part to find the Max date for a project and the other part to obtain the related information. You can do this with a sub query or with nested queries. I will include an example of nested queries because they are more efficient and easier for most people to understand and test.

Query1:
Select PROJ_ID, Max(UPDATE_DATE) As MaxUPDATE_DATE
From YourTable;

Query2:
Select q.PROJ_ID, q.MaxUPDATE_DATE, t.WEEKLY_UPDATE
From Query1 as q Inner Join YourTable as t ON q.PROJ_ID = t.PROJ_ID;

Bear in mind that if there is more than one entry for the combination of PROJ_ID and UPDATE_DATE, all of them will be returned.