Problem with a 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
 
Try this query:

SELECT tblProducts.Tank, Max(tblProducts.Date) AS MaxOfDate,
Last(tblProducts.Product) AS LastOfProduct
FROM tblProducts
GROUP BY tblProducts.Tank;
 
If you are just trying to sort by date , the in the design grid of your
query, in the [Date] field click in the sort row and select descending.

If you just want the item with the latest date try using the DMax function.
something like (untested):

SELECT myTable.Tank, myTable.Date, myTable.Product FROM myTable
WHERE (((myTable.Date)=DMax("Date]","myTaqble")));

Substitute your actual table name and field name. FYI, it is not a good idea
to use "date" as a field name. Date is a reserved word in access, and you
could cause problems for yourself.

Hope this helps
Rosco
 
Michael Kintner said:
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!!!
Click to expand...

There are several ways to query this. Here's one:

SELECT
tblTanks.Tank,
tblTanks.TankDate,
tblTanks.Product
FROM
tblTanks
INNER JOIN
[
SELECT
tblTanks.Tank,
Max(tblTanks.TankDate) AS MaxOfTankDate
FROM
tblTanks
GROUP BY tblTanks.Tank
]. AS T
ON
(tblTanks.TankDate = T.MaxOfTankDate)
AND
(tblTanks.Tank = T.Tank)
ORDER BY
tblTanks.Tank;
 
Back
Top