Stock Level subtract Order Quantity?

[Sam H]

Hi,
Im designing a database for a large adhesives company, and I need help in
writing a query to subtract the quantity of an order which will be specified
in a form from the stock level in the product list table.

Thanks in anticipation,
Merry Xmas,
Sam

 

[Allen Browne]

Sam, this may be more complex than you anticipated. Not only do you need
to remove an item when a new row is entered, but consider how you will
handle cases such as:
- Restore the item if the user deletes the row from the work order parts.
- Alter the quantity removed if the user changes an existing record.
- Restore the old part, and remove the new one if the user changes the part
number on an existing record.

In my view, it is better to get Access to calculate the quantity when needed
rather than store the quantity. Here's an example of how that's done:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html