Inventory: last stock take qty

[Michael Wong]

I am trying to display the products and their last stock take qty.
Currently, I've worked out a query that does this, by using several queries.

I am wondering, is there a faster method to do it? Or should I review my way
of doing inventory?

Thank you for any help or comments.

Here is what I have:

TProduct (ProductId (PK), Description,...)
TStockTake (StockTakeId (PK), StockTakeDate, ProductId)
TStockTakeDetail (StockTakeDetailId (PK), Quantity, Notes)

The queries are (first 2 that I depend on and the final result):

QLastStockTake:
SELECT T.ProductId, Q.StockTakeId, Q.StockTakeDate
FROM TProduct AS T LEFT JOIN TStockTake AS Q ON T.ProductId=Q.ProductId
WHERE (((Q.StockTakeDate) Is Null Or (Q.StockTakeDate)=(SELECT
MAX(S.StockTakeDate) FROM TStockTake AS S WHERE S.ProductId =
T.ProductId)));

QStockTakeQty:
SELECT TStockTakeDetail.StockTakeId, Sum(TStockTakeDetail.Quantity) AS
Quantity
FROM TStockTakeDetail
GROUP BY TStockTakeDetail.StockTakeId;

QProductWithLastQty:
SELECT TProduct.ProductId, TProduct.Description,
QLastStockTake.StockTakeDate, QStockTakeQty.Quantity AS Qty
FROM QStockTakeQty RIGHT JOIN (TProduct INNER JOIN QLastStockTake ON
TProduct.ProductId = QLastStockTake.ProductId) ON QStockTakeQty.StockTakeId
= QLastStockTake.StockTakeId
ORDER BY TProduct.Description;

 

[JeffW]

take a look at Allen Browne's inventory function. I use it in one of db's
(slightly modified) to track inventory in multiple locations.

http://allenbrowne.com/AppInventory.html

JW

 

[Michael Wong]

Thank you for your reply.
I've already taken a look at Allen Browne's site and also applied his idea
to a query. It works very well, except that my query is a bit slow when
accessed via a network..

Anyway, I'll just do with it for the now.