There is a better way. What you are doing is storing a calculated field,
which is generally considered a bad design in any table. You should not be
risking loss of integrity in your data by calculating and recalculting
amounts.
Inventory management can be one of the more complex applications to manage,
but the essential issue here is that you need only Two inventory adjustment
fields, not three. Those fields are "AddUnits" and "AddReason", or more
probably a different set of field names better suited to the requirement,
such as "Adjustment" and "AdjustmentReason". That allows you to track both
positive changes (Inventory received from vendors) and negative changes
(shrinkage).
When units are added to, or subtracted from stock, you insert a record into
AddUnits and an ID to indicate the reason for the adjustment. The SUM of all
such additions is your UnitsInStock before adjustments for sales,
adjustments, etc. Sales records are added, I assume, in a separate sales
transaction table, and the same process can be applied to it.
The basic query to return that would be:
"Select Product, SUM(Adjustment) AS TotalAdjustments FROM tblInventory
GROUP BY Product"
To account for sales, of course, you need something similar:
"Select Product, SUM(QuantitySold) AS TotalSales FROM tblSaleDetails
GROUP BY Product"
To get your current Units in Stock, you combine the two, subtracting total
sales from total adjustments:
Select Product, SUM(tblInventory.Adjustment) -
SUM(tblSaleDetail.QuantitySold) as UnitsInStock
FROM tblInventory LEFT JOIN tblSaleDetails ON tblInventory.ProductID =
tblSaleDetail.ProductID
GROUP BY Product
Whereever you need the current Units in Stock amount, you get it from this
query.
I am aware that there are other approaches to inventory tracking, and that
the exact implementation depends to a certain extent on how your business
operates. Nonetheless, this is the basic approach you need to take.
George