Looking for MaxDate, its most recent date and the interval between. Of the MaxDate and most recent date, I also need the quantities for each so I can also find the interval of them too.
Table "tblITEM_InventoryCount" structure is as follows:
Item_No Count Date Quantity
001 08/29/2015 12
001 08/15/2015 17
001 07/15/2015 19
I need a Query written that will return the following results:
Item_No: 001
Max(CountDate): 08/29/2015
PriorCountDate: 08/15/2015
Interval Days (Max(CountDate)-RecentDate): 14
Max(CountDate) Quantity: 12
PriorCountDate Quantity: 17
Interval Qty (17-12): 5
Currently using first query to find last two count dates for each ITEM_NO called "qryLAST2_InventoryCount_TRANSACTIONS":SELECT tblITEM_InventoryCount.ITEM_NO, tblITEM_InventoryCount.Quantity, tblITEM_InventoryCount.CountDate
FROM tblITEM_InventoryCount
WHERE (((tblITEM_InventoryCount.CountDate)>=NthInGroup([tblITEM_InventoryCount].[ITEM_NO],2)))
ORDER BY tblITEM_InventoryCount.ITEM_NO, tblITEM_InventoryCount.CountDate DESC;
Then using second query to calculate the results:
SELECT qryLAST2_InventoryCount_TRANSACTIONS.ITEM_NO, qryLAST2_InventoryCount_TRANSACTIONS.CountDate, (SELECT MAX([CountDate]) FROM [qryLAST2_InventoryCount_TRANSACTIONS] AS [Old Orders] WHERE [Old Orders].[CountDate] < [qryLAST2_InventoryCount_TRANSACTIONS].[CountDate] AND [Old Orders].[ITEM_NO] = [qryLAST2_InventoryCount_TRANSACTIONS].[ITEM_NO]) AS PriorCountDate, DateDiff("d",CountDate,PriorCountDate) AS DaysInterval, qryLAST2_InventoryCount_TRANSACTIONS.Quantity, (SELECT Last([Quantity]) FROM [qryLAST2_InventoryCount_TRANSACTIONS] AS [OldCount] WHERE [OldCount].[Quantity] < [qryLAST2_InventoryCount_TRANSACTIONS].[Quantity] AND [OldCount].[ITEM_NO] = [qryLAST2_InventoryCount_TRANSACTIONS].[ITEM_NO]) AS PriorQuantity, [Quantity]-[PriorQuantity] AS QuantityInterval, [QuantityInterval]*30/[DaysInterval] AS [Usage]
FROM qryLAST2_InventoryCount_TRANSACTIONS
GROUP BY qryLAST2_InventoryCount_TRANSACTIONS.ITEM_NO, qryLAST2_InventoryCount_TRANSACTIONS.CountDate, qryLAST2_InventoryCount_TRANSACTIONS.Quantity
ORDER BY qryLAST2_InventoryCount_TRANSACTIONS.ITEM_NO, qryLAST2_InventoryCount_TRANSACTIONS.
I am not getting the results I need. The query returns two record lines for each item along with their max or last countdate, the previous countdate, intervaldays, quantity, last quantity, and interval.
I need max or last countdate and its quantity count and prior countdate and its quantity count. I need a Query written that will return the following results:
Item_No: 001
Max(CountDate): 08/29/2015
PriorCountDate: 08/15/2015
Interval Days (Max(CountDate)-RecentDate): 14
Max(CountDate) Quantity: 12
PriorCountDate Quantity: 17
Interval Qty (17-12): 5
Any help would be greatly appreciated.