find all 5 fields with Max date

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

--
Misty :-)

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

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
;
--
KARL DEWEY
Build a little - Test a little


"Mitchell_Collen via AccessMonster.com" wrote:

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

--
Misty :-)

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1