Need max date and recent date and their respective quantities

[krsd0118]

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.

[June7]

You want to retrieve the two most recent dates for a group as well as the quantities from those records and calculate difference of quantities? Not going to be simple.

Review http://allenbrowne.com/subquery-01.html

Might need a combination of TOP N and GET THE VALUE IN ANOTHER RECORD.

Something like:

Query1
SELECT tblITEM_InventoryCount.ID, tblITEM_InventoryCount.Item_No, tblITEM_InventoryCount.CountDate, CDate((SELECT TOP 1 Dupe.CountDate FROM tblITEM_InventoryCount AS Dupe WHERE Dupe.Item_No=tblITEM_InventoryCount.Item_No AND Dupe.CountDate<tblITEM_InventoryCount.CountDate ORDER BY Dupe.CountDate DESC, Dupe.ID)) AS PriorDate
FROM tblITEM_InventoryCount;

Query2
SELECT Query1.ID Query1.Item_No, Query1.CountDate, tblITEM_InventoryCount.Quantity, Query1.PriorDate, tblITEM_InventoryCount_1.Quantity
FROM tblITEM_InventoryCount AS tblITEM_InventoryCount_1 INNER JOIN (tblITEM_InventoryCount INNER JOIN Query1 ON (tblITEM_InventoryCount.Item_No = Query1.Item_No) AND (tblITEM_InventoryCount.CountDate = Query1.CountDate)) ON (tblITEM_InventoryCount_1.Item_No = Query1.Item_No) AND (tblITEM_InventoryCount_1.CountDate = Query1.PriorDate);

The above in one
SELECT tblITEM_InventoryCount.ID AS MaxID, Query1.Item_No, Query1.CountDate, tblITEM_InventoryCount.Quantity AS MaxQuantity, tblITEM_InventoryCount_1.ID AS PriorID, Query1.PriorDate, tblITEM_InventoryCount_1.Quantity AS PriorQuantity
FROM tblITEM_InventoryCount AS tblITEM_InventoryCount_1 INNER JOIN (tblITEM_InventoryCount INNER JOIN (SELECT tblITEM_InventoryCount.Item_No, tblITEM_InventoryCount.CountDate, CDate((SELECT TOP 1 Dupe.CountDate FROM tblITEM_InventoryCount AS Dupe WHERE Dupe.Item_No=tblITEM_InventoryCount.Item_No AND Dupe.CountDate<tblITEM_InventoryCount.CountDate ORDER BY Dupe.CountDate DESC, Dupe.ID)) AS PriorDate FROM tblITEM_InventoryCount) AS Query1 ON (tblITEM_InventoryCount.CountDate = Query1.CountDate) AND (tblITEM_InventoryCount.Item_No = Query1.Item_No)) ON (tblITEM_InventoryCount_1.CountDate = Query1.PriorDate) AND (tblITEM_InventoryCount_1.Item_No = Query1.Item_No);

I am having difficulty using the last query as source for a TOP N to pull only one record for each group.