Prev: same error
Next: make query to return a subset only if not in another subset
From: Mitchell_Collen via AccessMonster.com on 27 Jun 2008 16:58
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/Forums.aspx/access-queries/200806/1

From: KARL DEWEY on 27 Jun 2008 19:20
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/Forums.aspx/access-queries/200806/1
>
>
 | 
Pages: 1
Prev: same error
Next: make query to return a subset only if not in another subset