OK, I am trying to use the Max function in a query under the total section. I would like one record with the highest or "Max" date. I would like all the other columns to follow suit, but I can't get them to do so.

Sample Data:

ID DATE ID2
466 5/1/11 16
466 3/1/11 1
466 4/1/11 21

If I set Max on the date it gives me:
466 5/1/11 16
466 5/1/11 1
466 5/1/11 21

If I set Max on the date and ID2 it gives me:
466 5/1/11 21

What I want is:
466 5/1/11 16

How do I get there?

Hi,

What version of Access are you using?

Try something like:

SELECT ID, [Date], ID2
FROM TableName
WHERE [Date]=DMax("Date", "TableName")

(untested)

By the way, "Date" is a *reserved* word in Access. Consider using a different name for your field.

Hope that helps...

Posting the SQL for the queries below would help us to help you. With the proper table name, the following might be what you need.

Select * From Whitechair
where Whitechair.[Date] IN
(SELECT Max(Whitechair.[DATE]) AS MaxOfDATE
FROM Whitechair
GROUP BY Whitechair.ID);

It uses a subquery to grab the max date and use it as the criteria for the query. You could use a DMax function to do the same.

BTW: Watch out for using Date as a field name. It's a reserved word and might give you problems. Notice how I put [] around it in the SQL.

Alright. I'll post the SQL here to see what you can do. I played around with what you wrote and it worked until I added another column. Then it put more records in there than what I want.


SELECT tblItemDesc.tblItemDescID, tblItemDesc.ItemDesc, tblItemDesc.ItemSerialNo, Max(tblShipmentInfo.PickUp) AS MaxOfPickUp, tblShipmentInfo.tblShipmentInfoID, tblShipTo.ShipToName
FROM tblShipTo RIGHT JOIN (tblShipmentInfo INNER JOIN (tblItemDesc RIGHT JOIN tblItemInfo ON tblItemDesc.tblItemDescID = tblItemInfo.ItemDescID) ON tblShipmentInfo.tblShipmentInfoID = tblItemInfo.IIShipmentInfoID) ON tblShipTo.tblShipToID = tblShipmentInfo.ShipToID
GROUP BY tblItemDesc.tblItemDescID, tblItemDesc.ItemDesc, tblItemDesc.ItemSerialNo, tblShipmentInfo.tblShipmentInfoID, tblShipTo.ShipToName;

So I need the most recent PickUp for each tblItemDescID

Let me know what you think.

By the way, I didn't name anything Date, it was only used for the example, the date is actually called PickUp, which is the date the item was picked up.

See The Access Web article Getting a related field from a GroupBy (total) query.