If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
find all 5 fields with Max date
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 |
Thread Tools | |
Display Modes | |
|
|