find all 5 fields with Max date

[Mitchell_Collen via AccessMonster.com]

Hi

I want to create a query that will return 5 fields that have the max date or
latest date. I can't get the following to work though.

Select Drug, Location, Total_On_hand, min, max, Datetime
From InMachine
Where Max(Datetime)

I don't have to show the Datetime, I just need to see in all 200 locations
and what drug and Totals on hand were for a quick snap shot.

Please help me.
Thanks.
Misty

 

[KARL DEWEY]

Use two queries unless you know subqueries ---
Save this query as Last_Drug_Loc.
Select Drug, Location, Max([InMachine].[Datetime]) AS Last_Date
From InMachine
GROUP BY Drug, Location;

Select Drug, Location, Total_On_hand, min, max, Max([InMachine].[Datetime])
AS Last_Date
From InMachine INNER JOIN Last_Drug_Loc ON [InMachine].[Drug]=
Last_Drug_Loc.Drug AND [InMachine].[Location]= Last_Drug_Loc.Location AND
[InMachine].[Datetime]= Last_Drug_Loc.Last_Date
;