Is there a better way to do this.
Bless you, there is - but it takes some work. It is a non-trivial conversion but it is excruciatingly correct if you get it working. And always right no matter what happens as long as the DB stays intact.
In the "correct" form of an inventory database, "stock on hand" is merely the running sum of all inventory adds and inventory removes. This number NEVER can properly be stored in a table as a single entry per part number. There are technical and practial reasons for this.
Technically, your inventory record showing part number xyz123 has on-hand balance of 12345 units is valid ONLY at a particular time. If that time is not part of the key, you have violated database normalization rules.
Practically, this means that if you tried to make time part of the key, you have to rewrite (therefore duplicate) lots of data. Talk about DB bloat!
In theory an inventory transaction either adds or subtracts counts/units from the count on-hand for the specific item. We in this forum have discussed inventory systems many times. Searching for keyword "Inventory" ought to get you a few dozen to a few hundred hits. Some of the discussions have been very lively. Some even a bit heated.
The way that this problem is USUALLY handled is that you identify several types of inventory transaction.
1. Add units via satified purchase order from vendor i.e. you resupplied your stock by buying some more.
2. Add units via restock or adjustment. This can be treated as a single transaction type or as two sub-types, either of which puts stock "back on hand" without buying it.
3. Remove units via satisfied sales order to customer i.e. you sold something to a customer.
4. Remove units via shrinkage or adjustment. This can be treated as a single transaction type or as two sub-types, either of which takes stock "off the shelf" without selling it.
plus one more that some folks prefer for inventory DBs that archive old stock transactions -
5. Statement of stock balance at reconciliation/inventory assessement time.
You talked the case where you want to commit X units, so you would have one more type of transaction
6. Tentative removal of units via attempted sales order/requisition
However, this type eventually vanishes in one of two ways - deletion (sales order was cancelled) or converted to type 3 - satisifed sales order.
Now, at this point, you have to have a date and time, part number, and quantity in every one of these transactions. Then inventory is merely the running sum of the quantity field for a union query that selects each of these record types and applies the appropriate + or - sign to the quantity. Obviously, types 1 & 2 have + and types 3 & 4 have -. Type 5 is always +. Type 6 is always -.
And if you have transactions of type 6 in the union query and the on-hand count comes out negative, you can't immediately fill the tentative order.
Then the only remaining wrinkle is that once per archiving period, you freeze the DB, eliminate any of the type 6 (tentative or uncommitted) records; do a hand count; do one of the two possible adjustment transactions to rectify the count; write a "balance at time X" record (type 5); and finally, archive and immediately DELETE all records for that stock part number with transaction dates earlier than time X. Leaving ONE AND ONLY ONE balance record in the account for that part number. Your sales history is in the table that receives your archived transactions, so there is no data loss.
Wrinkles on this technique might include that at the end of the month you only compute the balance to the beginning of the current month, write the "balance at time X" for X=beginning of the month, and leave the last month of subsequent transactions in the DB so you can have a history. The point is that the "balance at time X" transaction is always the earliest thing you've got fir a given part number when the DB is back up and running again 'cause you have already archived everything earlier and rolled up the numbers to correctly populate that record.
This method then requires you to just do a SUM of the UNION query that pulls in the several possible sources of transactions. It DOES NOT ASSUME that all transactions are in the same table. It only requires that the UNION query can gather the required items from their respective locations and impose a common format on them so a SUM query can be written.
OK, does this sound daunting? (Probably.) But if you understand the real meaning of normalization, you must also realize that if you DON'T take this approach, then technically your tables will NEVER be normalized due to their IMPLIED time depencies that can never be efficiently recorded in the master inventory tables. And in this case, you are guaranteed any one of several bad things:
A. DB bloat by repeating inventory records with different times
B. Destructive interference among competing processes
C. Incorrect on-hand counts that occur after symptom B occurs and one of the two update transactions gets overwritten by another one at nearly the same time.
D. Heavy lock-based delays and form errors because you have to update records so frequently that some records just always stay locked, or so it would seem.
