Update button

[collen]

i have created a simple access POS database and have the following tables products and order details, orders amongst others, the products table has a unitsinstock column and the order details has a quantiy column. What im trying to do is create an update query that i will execute once a day via a button at the end of the day that will sum the number of units sold per productid and subtract this value from the units in stock(products.unitsinstock) per unit productid...the select statements below gives these values but when i try to create an update table based on this it doesnt work, my update query is as follows:-

Update Query

UPDATE products SET products.UnitInStock = products.UnitInStock - (
SELECT SUM([order details].Quantity)
FROM [order details]
WHERE [order details].ProductID = products.ProductID);

units sold

SELECT Orders.OrderDate, [Order Details].ProductID, Sum([Order Details].Quantity) AS [Total Sold]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDate, [Order Details].ProductID

Units in Stock

SELECT Products.ProductID,
Products.ProductName, Products.CategoryID,


Products.QuantityPerUnit,
Products.UnitsInStock, Products.ReorderLevel
FROM Products;

any help will be appreciated, im trying to avoid having to subtract the products.unitsinstock=(products.unitsinstock -[order details].quantity) every time a product is sold

[jzwp11]

The approach you are suggesting is not the best way to handle inventory. Please check out Allen Browne's site for handling inventory on hand

[collen]

If my POS was big i would try and adopt the Allens solution..The thing is i dont even have an Invoice table let alone a stock table or aquisitions table. Currently what im doing which is rather cumbersome is to run the All stocks sold query which limits sold items for the current date and i have another query that selects productid, productname and unitsinstock, i then manually adjust the unitsinstock field....this works fine but the process is rather long especialy if i sold a lot of items hence i am trying to automate a process im currently doing manual

[jzwp11]

The best approach is to not have to update your units in stock at all, but just calculate the current amount available. If you don't want to use Allen's approach then the only thing I can suggest is to use your UnitInStock field as your base inventory value and never change it (so it will not technically represent your units in stock but rather a beginning inventory value). You would then calculate the available amount by subtacting all units sold from that base inventory value.

query name: qryQuantityConsumed
SELECT [Order Details].ProductID, Sum([Order Details].Quantity) AS TotalUnitsSold
FROM [Order Details]
GROUP BY [Order Details].ProductID


query name: qryProductList
SELECT Products.ProductID, Products.UnitsInStock
FROM Products;

Final query

SELECT UnitsInStock as BeginingInventory, IIF(IsNull(TotalUnitsSold),0, TotalUnitsSold) as UnitsSold, BeginningInventory-UnitsSold as UnitsOnHand
FROM qryProductList left join qryQuantityConsumed on qryProductList.ProductID=qryQuantityConsumed.produ ctID

You can then use the DLookup() function to bring in the UnitsOnHand value when you need it.

Although this is one way, I still strongly recommend altering your table structure to accommodate Allen's approach as it is the more robust way of handling this.