Update Query

[3239]

I am trying to build a database that will keep track of purchase orders by my company and orders by customers. I have a table called Inventory that contains the product name and number of units in stock. I am trying to use an update query to add the number of items order by my company to the Inventory table. And I am also trying to use an update query to deduct the number of items ordered by the customer, from the Inventory table. So my goal is to keep the Inventory table updated when items are added or taking away from it.

I have two queries, Query1 is supposed to subtract the number of items from the inventory table when an order is placed by a customer. I entered this in the Update To field, [Stock]-[Quantity]

the second query is Query2 and it is supposed to add items that are ordered by my company to the Inventory table. I have this in the Update To field,[UnitsOrdered]+[Stock]

I am not getting the results I am looking for when I run the queries.


I have attached the database for anyone who would like to help me with this problem.

 

[neileg]

Before you go any further, I have to tell you that your design is wrong. If you try to store the balances in a table you will end up with problems when the update runs once too often, or fails to run at all. You should calculate the balance in a query or a form when you need to.

Search in here for inventory or stock control to see lots of postings on this matter.