Provided by Allen Browne. Updated April 2010.
Inventory Control: Quantity on Hand
How do I update the Quantity On Hand each time an item is sold/used? The simple answer is: You don’t! You calculate the value when you need it.
The calculation is very simple: the total number acquired, less the number disposed of. Just use DSum() on the table of acquisitions to get the number acquired for any product. Another DSum() expression on the table of uses/invoices gets the number disposed of. For the simplest databases, that’s all you need.
For heavily used databases, there are two practical problems :
- Over the years, recalculating all values from the beginning becomes slow .
- Outside the realm of pure maths, items go missing .
To solve the second problem, most companies do periodic stocktakes. The stocktake gives you a known starting point. so it solves the first problem too: you can limit your calculation to transactions since then. Storing the stocktake quantities is not denormalizing: any difference between the stored stocktake value and a pure calculated value is not a lack of data integrity. It’s valuable information that may be worth reporting on.
The quantity on hand is therefore:
- quantity as of the last stocktake,
- plus acquisitions since then,
- less disposals since then.
Even if you don’t actually count stock, you can still take this approach to provide known values at a certain date, avoiding the need to recalculate everything from the beginning.
A typical database has an Invoice table and an Invoice Detail table, so one invoice can have many lines. (If this is unfamiliar, see the Order and Order Detail tables in the Northwind sample database.) In the same way, a single incoming delivery may include many products, so you need Acquisition and Acquisition Detail tables. With the Product table and Stocktake table, you have a structure like this:
This function returns the quantity on hand for any product, optionally at any date.
Storing the Quantity On Hand instead
One of the basic rules of data normalisation is not to store dependent values. Break the rule at your own peril: it takes more work than doing the job properly; it diminishes the queryability of your data; it leaves you without any certainty that your answer is correct.
If you still want to store a QuantityOnHand value, you must update it on every insert, edit, and delete, without exception. If you want to go that route, here’s an overview of what’s involved.
In the AfterInsert event of your form, create a dynamic update query statement to subtract the Quantity used/sold on this row from the QuantityOnHand in the Product table.
If the user changed either the quantity or the product, you have work to do. In the form’s BeforeUpdate event, examine the OldValue of these controls. If either changed, set a module-level flag and capture the old values into a pair of module level variables. Then in the form’s AfterUpdate event, check the flag and execute an UPDATE query to increment/decrement the quantity by the difference between the Value and the OldValue. Reset the flag.
In the continuous subform, multiple records can be selected for deletion. The Delete event fires for each record, but you do not know whether the deletion will be cancelled. The BeforeDelConfirm and AfterDelConfirm events fire once for the batch, but the record(s) being deleted are not available at this stage. You need both events and a temporary table.
In the Delete event of the form, append a record to a temporary table to store the product and quantity being deleted. In the AfterDelConfirm event, if the Status is acDeleteOk, execute an update query that joins the temporary table to the Product table and increases the QuantityOnHand by the Quantity being deleted. Then delete all records from the temporary table, whether the delete proceeded or not.
For this to work, you must ensure that the user does not turn off the confirmation options (Tools | Options | Edit/Find | Confirm in Access 95 – 2003; Microsoft Office button | Access Options | Advanced | Confirm in Access 2007; File | Access Options | Advanced | Confirm in Access 2010.) To complicate matters further, there is a bug in Access 2, 95, and 97 (all patches) such that it may point to the wrong record in the AfterInsert and AfterUpdate events, so you will have to code around that bug as well.