maxDate sum

[ShostyFan]

I have an inventory table with products, dateOfCount, and Qty.


If this is the input:
product1 6/1/2015 1
product1 6/1/2015 1
product1 5/1/2015 1

I want a query to sum the qty of only the MaxDate, so in the example I gave, the total Qty would be 2.

Currently I can output the max date just fine, but when I sum the Qty I keep getting 3 as my output.
Thanks.

[June7]

Could try a TOP N nested subquery. Review: http://allenbrowne.com/subquery-01.html#TopN

[ShostyFan]

I went through your link but I couldn't get the concepts to work for me.

[June7]

Try:

SELECT * FROM Inventory WHERE Product & ":" & DateOfCount IN (SELECT TOP 1 Product & ":" & DateOfCount FROM Inventory AS Dupe WHERE Dupe.Product=Inventory.Product ORDER BY Dupe.DateOfCount DESC, Dupe.Product DESC);

or

SELECT Inventory.Product, Inventory.DateOfCount, Sum(Inventory.Qty) AS SumOfQty
FROM Inventory
GROUP BY Inventory.Product, Inventory.DateOfCount, [Product] & ":" & [DateOfCount]
HAVING ((([Product] & ":" & [DateOfCount]) In (SELECT TOP 1 Product & ":" & DateOfCount FROM Inventory AS Dupe WHERE Dupe.Product=Inventory.Product ORDER BY Dupe.DateOfCount DESC, Dupe.Product DESC)));