# Thread: Calculate inventory only on last StockTake Date

1. ## Calculate inventory only on last StockTake Date

Hello! It's been a long time since I've been on the Lounge, but I am working on updating our database and need some help. I need to calculate the inventory on hand only for the last stock take date, not all dates. This query separates the quantities by date, but I only want the last date, not all of them.

SELECT tblProducts.ProductID, Sum([tblStockTake].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock, tblStockTake.StockDate, Max(tblStockTake.StockDate) AS MaxOfStockDate
FROM tblProducts INNER JOIN tblStockTake ON tblProducts.ProductID = tblStockTake.ProductID
GROUP BY tblProducts.ProductID, tblStockTake.StockDate;

ProductID InStock Date Stock Taken MaxOfStockDate
TRAENGHHA 108,000.00 9/26/13 9/26/13
TRAENGHHA 90,000.00 10/1/13 10/1/13
TRAENGPDIQAA 11,250.00 9/26/13 9/26/13
TRAENGPOMIATW 198,000.00 9/26/13 9/26/13

The values in red are what I want. In this case I only have 1 product that was entered twice, to use as an example. Anybody have an answer for me?

2. Maybe this will do it:

Code:
```SELECT P.ProductID, Sum([T].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock, T.StockDate, Max(T.StockDate) AS MaxOfStockDate
FROM tblProducts P INNER JOIN tblStockTake T ON P.ProductID = T.ProductID
WHERE T.StockDate = (SELECT Max(StockDate) FROM tblStockTake T2 WHERE T2.ProductID = P.ProductID)
GROUP BY tblProducts.ProductID, tblStockTake.StockDate;```

3. Ruirib,

I tried your SQL statement, after creating the two tables and entering some test data, but I received an error message that reads:

"You tried to execute a query that does not include the specified expression 'ProductID' as part of an aggregrate function."

I thought you were close, so I tried a slight variation of your suggested SQL statement. This seems to return the desired result, with the very limited test data at hand:

Code:
```SELECT P.ProductID,
Sum([T.UnitsInStock]*[P.QtyPerBox]) AS InStock,
T.StockDate, Max(T.StockDate) AS MaxOfStockDate

FROM tblProducts AS P INNER JOIN tblStockTake AS T ON P.ProductID = T.ProductID

WHERE T.StockDate=
(SELECT Max(StockDate) FROM tblStockTake T2 WHERE T2.ProductID = P.ProductID)

GROUP BY P.ProductID, T.StockDate;```

4. My apologies for not responding! We've been having some network issues and I've been involved with that.

I took what ruirib suggested and tweaked it to the code below and that works:

Code:
```SELECT tblProducts.ProductID, tblProducts.ProductName, Sum([tblStockTake].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock
FROM tblProducts INNER JOIN tblStockTake ON tblProducts.ProductID = tblStockTake.ProductID
WHERE (((tblStockTake.StockDate)=(Select Max(StockDate) From [tblStockTake] where [tblProducts].[ProductID]=[tblStockTake].[ProductID])))
GROUP BY tblProducts.ProductID, tblProducts.ProductName;```

5. Yes, my code, without testing, had a mistake that you both found. I would have used the first solution, with the Group By P.ProductID, posted by Tom, simply because it's more easy to read and that is what I wanted to type initially.

Sorry about the mistake, these things usually get caught if you are able to test it and I confess I did not .

6. Hi 79shultz,

You might want to have a look at these two subquery help topics, written by former Access MVP Allen Browne:

Subquery basics
http://allenbrowne.com/subquery-01.html

Surviving Subqueries
http://allenbrowne.com/subquery-02.html

Allen's site is a fantastic site for learning about Microsoft Access.
http://allenbrowne.com/tips.html

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•