Actually, what you are proposing is not a sound approach to inventory
management in this environment.
This is a fairly broad and complex subject, so you might benefit from some
background reading on managing inventory in a database, as well as table
design, but basically, here's how it should be handled.
The Inventory table has a field for the product being tracked, e.g.
ProductID.
The Inventory table has a field for "AdjustmentAmount", in which all
additions to and subtractions from inventory are entered as positive or
negative values.
The Inventory table has a field indicating the reason for the adjustment,
e.g. "Shipment Received", or "Sale Fulfilled".
The Inventory table has a field for the date on which the adjustment is
made, e.g. "TransactionDate"
At any given point, "StockOnHand" is a calculated value and is the result of
summing all "AdjustmentAmounts" in a query by ProductID.
In most systems, there will be periodic reconciliations between the
inventory calculated in the database and an actual count of items on hand in
the warehouse. An adjusting amount is added to the inventory table to bring
it into alignment with actual stock on hand on that date.
As noted, inventory management can be quite complex, so it would be good to
do some additional research before committing to a table design for your
application.
George
