Access Query

  • Thread starter Thread starter Michael Kintner
  • Start date Start date
M

Michael Kintner

I am having a problem with this query.

I would like to show the latest product in all the tanks based upon the
latest date for that tank.

Table:
Tank Date Product
43 12/1/04 ABC
45 12/1/04 DEF
46 12/2/04 KLM
45 12/3/04 DEF
43 12/3/04 ABC
43 12/6/04 XYZ


Result would be:
Tank Date Product
43 12/6/04 XYZ
45 12/3/04 DEF
46 12/2/04 KLM

Can someone please help me. Thank you in advance!!!
Happy Holidays...
Mike
 
Hi Michael,

This is probably not the most eloquent answer, but it works.
Create a query with just Tank and Date. Click on the totals button.
Keep the "group by" on Tank. Select "Max" for Date. Save this query.
Create a 2nd query. This query should have the query above joined to
your original table by Tank and Date. In the new query, select Tank and Date
from the query and select Product from the table. This should give you the
results you're looking for.
 
Dear Michael:

How about this:

SELECT Tank, Date, Product
FROM YourTable T
WHERE Date = (SELECT MAX(Date) FROM YourTable T1
WHERE T1.Tank = T.Tank)
ORDER BY Tank

If what you are wanting is to see only the most recent row for each Tank,
this should do it.

If you have two rows for a tank with the same date, and when that is the
most recent date, then you will see both, even if both are for the same
Product..

Tom Ellison
Microsoft Access MVP
 
Back
Top