hfsitumo2001
Member
- Local time
- Today, 01:45
- Joined
- Jan 17, 2021
- Messages
- 394
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.
Code:
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","");