Full Version: Inventory not updating
UtterAccess Forums > Microsoft® Access > Access Forms
On my inventory database, after a sales order is made and a qty of a product (products are selected by a query
to populate a combo for selection) is selected, the tblInventory does not reduce the qty of the item. How do i
manage to go about doing this so that when i select a qty of product, the changes reflect the QtyonHand in the
You should not be storing the quantity on hand, you should be calculating it when needed via a query.
Larry Larsen
Check out: Inventory Control: Quantity on Hand by Allen Browne.<
The link talks abt DSum(), but im not sure exactly where that would be used? And moreover, i do understand now
That the inventory qty is calculated value, and i do use a query to select the product, qty, and its other fields but
I am unsure of exactly how to make this work.
Any suggestions please?
Larry Larsen
THe speaks of using the DSum() as a very simple quick easy method.. of calculating your current stock.
It query level:
Stock In: DSum("Item","StockInTable",Criteria here if needed)
Stock Out: DSum("ItemSold","StockOutTable",Criteria here if needed)
Stock In - Stock Out = Current stock

How does this all relate to the example of Northwind Traders? I can't seem to find the query and DSum method they used, if thats even what they used?
Northwind never 'reduced' the Qty, did it? It's only a 'starting' place db.
Northwind wasn't the 'Gawd' for example's to an order/inventory system.........Just a start.
You have to remember, Northwind came from Bill Gate's company. When was the last time he/company gave up anything for 'free'?
A has more to offer in the long run.
In my humble opinion, and I thank all that have helped me over the last year getting back into Access. frown.gif
Thanks for your info about the database, but i need help with my situation and thats why i use utteraccess.
There do i use this? Can someone maybe explain this to me in words and or an example of how to store the qty as a calculated value?
Larry Larsen
The three examples I gave are used in a query..

(3) Columns:

Stock In: DSum("ItemIn","StockInTable")¦Stock Out: DSum("ItemSold","StockOutTable")¦Current Stock:Stock In - Stock Out

Edited by: Larry Larsen on Mon Jan 2 5:38:47 EST 2006.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.