Inventory query not generating correct QTY sold for LOT

[socalreolist]

Hello,



Having a problem with my inventory query.

The product (Aldavine) Lot (7889409) has a purchase qty of 4 and a qty sold of 4, but the product (Aldavine) Lot (8682110) does have a purchase
qty of 7 but has not sold yet.

My inventory transaction table seems correct.

What kind of info should I include so someone could analyze my code..

Below is the SQL for my Inventory Query. I can include other code..

thanks,
Dan



SELECT Products.ID AS [Product ID], Products.[Product Name], Products.[Product Code], [Inventory Purchased].LotNumber, Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz([Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level], Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level], IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To Reorder]
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold].[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID = [Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON Products.ID = [Products On Back Order].[Product ID];

[socalreolist]

Here is my query for inventory.



Also here is my inventory transactions table, which has the transactions correct.. Just need to run a query that shows correct qty's.

[socalreolist]

More info on my design..

Using the Northwind database template and trying to customize for our company. I was able to add a field called Lot Number to the purchase order details table and order details table. The afterupdate code is adding the lot number to the inventory transactions table just fine, only thing is that when I run a query against the inventory transactions table, I cant seem to get the qty sold field to just apply to one lot instead of many lots of the same product.

[socalreolist]

Below is a screenshot of my inventory query which shows the Product ID (2) Could this be why the qty sold is showing up for both lot numbers?