Stock Report with supplier

[hfsitumo2001]

The following is my query for stock report. Basically is just base on one inventory table, we make query just to have a calculated field . The Quantity on hand will be updated by stock out and stock in transactions. Below is my table relationship. my question is how can I make a query that can include supplier ( on the criteria last?) in the Stock report.

SELECT Inventory.ItemCode, Inventory.Description, Inventory.IUnitPrice, Inventory.QtyPerCase, Inventory.UOM, Sum(Inventory.Inv_Qty) AS StockInHand, [StockInHand]*[IUnitPrice] AS InventoryValue, Inventory.ReorderLevel, Inventory.TargetStockLevel, [TargetStockLevel]-[StockInHand] AS ReorderAmount, IIf(([Inv_qty]<[ReorderLevel]) And ([TargetStockLevel]<>0),"Low Stock Level","") AS Warning
FROM Inventory
GROUP BY Inventory.ItemCode, Inventory.Description, Inventory.IUnitPrice, Inventory.QtyPerCase, Inventory.UOM, Inventory.ReorderLevel, Inventory.TargetStockLevel, IIf(([Inv_qty]<[ReorderLevel]) And ([TargetStockLevel]<>0),"Low Stock Level","");

 

[The_Doc_Man]

If you always and only ever have a single supplier for any single given inventory item, you can write a JOIN query provided that there is a supplier code somewhere. If it ever happens that you have more than one supplier for a given inventory item, it gets a little more complicated. Which case do you have?

 

[hfsitumo2001]

If you always and only ever have a single supplier for any single given inventory item, you can write a JOIN query provided that there is a supplier code somewhere. If it ever happens that you have more than one supplier for a given inventory item, it gets a little more complicated. Which case do you have?

I have more than one suppliers for one item code. Bur for information the Quantity on hand is always updated by Suppliers transactions, and by customers ( Users) transaction with this code:
!Inv_Qty = !Inv_Qty - Nz(Me.Quantity, 0) for supplies out
!Inv_Qty = !Inv_Qty +Nz(Me.Quantity, 0) for supplies in

And for your information the relationship of the tables as you see above is from Inventory table to purchase order details to Purchase order then finally to supplier.

I appreciate if you could give me the sample of query. What I could imagine is there will be many supplier and customer lines if we see that in datasheet view, Maybe select distinct can mitigate the problems?

Thank you

 

[hfsitumo2001]

If you always and only ever have a single supplier for any single given inventory item, you can write a JOIN query provided that there is a supplier code somewhere. If it ever happens that you have more than one supplier for a given inventory item, it gets a little more complicated. Which case do you have?

Hi The_Doc_Man: I tried to let Query Wizard do it for me and the result is almost like what I expected, it is like what I said, it consists of many lines of the same Quantity on hand. But what I want is just one line per stock item and the supplier is just the last one, I included the date field of the purchase order table. And the problem how can I modify this query in order to give us only the last one. Select distinct by the last date. This is the SQL

SELECT QryStockOnHand_extendedforAccting.ItemCode, QryStockOnHand_extendedforAccting.Description, QryStockOnHand_extendedforAccting.StockInHand, QryStockOnHand_extendedforAccting.UOM, QryStockOnHand_extendedforAccting.IUnitPrice, QryStockOnHand_extendedforAccting.InventoryValue, QryStockOnHand_extendedforAccting.Supplier, PurchaseOrder.OrderDate
FROM QryStockOnHand_extendedforAccting INNER JOIN (PurchaseOrder INNER JOIN PurchaseDetails ON PurchaseOrder.[ID] = PurchaseDetails.[PODetailsID]) ON QryStockOnHand_extendedforAccting.[ItemCode] = PurchaseDetails.[ItemCode];

As shown in the picture below, that is the result of the above query, but I want the last date in order date.

Anyone in the community that can help me please?