I am not sure how your table is structured, but given what you are doing I would assume that you have a master item table of some sort that lists the items you have and then another column that shows how many are in stock (an inventory on hand column perhaps) and an order table that shows the items ordered and the quantity. If this is the case then all you need to do is run a query that subtracts the quantity ordered in the orders table from the quantity on hand in the items table. You could have this query run every time an addition or deletion is made, and simply put in some logic that controls for things like ordering more items than are in stock. So this is how I am viewing it.
Items
Item Quantity
OrderedItems
[Items Ordered] [Quantity Ordered]
The query would then be
-
UPDATE Items
-
INNER JOIN OrderedItems ON [Items].Item=[OrderedItems].[Items Ordered]
-
SET Items.Quantity = [Items].Quantity-[OrderedItems].[Quantity Ordered];
-
If the amount ordered is adjusted down you could do the exact same thing only change the query to add Items back instead of subtract, or you could utilize the same query by interpreting putting items back as ordering negative amounts. For instance if initially a quantity of 4 is selected and then the quantity is decreased by 2 it would be interpreted as ordering -2 items which would put 2 items back into the inventory on hand column.
Hopefully that helps if it's off the mark let me have a little more detail and I'll see what I can come up with.
49
