need Help with a query that gets the first and last date for each item 1

[DougP]

I need a query that gets the first and last date for each item.
Items are brought into stock in an on going basis. Need to find the first day and last day an item was bought in.
What this does is show the same date for all three
[LastOfArrivalDelShip Date], [FirstOfArrivalDelShip Date], [ArrivalDelShip Date]
Here is what I have so far.

SELECT [Available to Promise].Item, Sum([Available to Promise].Quantity) AS SumOfQuantity, [Available to Promise].Warehouse, [Available to Promise].[ArrivalDelShip Date], Last([Available to Promise].[ArrivalDelShip Date]) AS [LastOfArrivalDelShip Date], First([Available to Promise].[ArrivalDelShip Date]) AS [FirstOfArrivalDelShip Date]
FROM [Available to Promise]
GROUP BY [Available to Promise].Item, [Available to Promise].Warehouse, [Available to Promise].[ArrivalDelShip Date];

DougP
< I Built one

http://www.robots2000.com

 

[PHV]

Perhaps this ?

SELECT Item, Sum(Quantity) AS SumOfQuantity, Warehouse
, Max([ArrivalDelShip Date]) AS [LastOfArrivalDelShip Date]
, Min([ArrivalDelShip Date]) AS [FirstOfArrivalDelShip Date]
FROM [Available to Promise]
GROUP BY Item, Warehouse;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Thanks


DougP
< I Built one

http://www.robots2000.com