I have a database that I have been developing over a matter of time and am at the point where I really need to be able to track inventory (products received + products in stock - products shipped) to know my actual Qty on Hand status. I have a frontend/backend database and right now I’m trying to figure out how to show the Qty on Hand in my Products Form. The code below gives me the desired results, but I’d like to have just the Qty on Hand from that query in my Products Form. Is there a way to do that?
SELECT tblProducts.ProductID, tblProducts.ProductName, Sum([qryStockCount].[InStock]+[qryStockReceived].[QtyRec]) AS QtyOnHand
FROM qryStockReceived INNER JOIN (tblProducts INNER JOIN qryStockCount ON tblProducts.ProductID = qryStockCount.ProductID) ON qryStockReceived.ProductID = tblProducts.ProductID
GROUP BY tblProducts.ProductID, tblProducts.ProductName;