Update Query: Stock Control

[Guest]

Ok, ive got a table called tblProductDetails and a query in which a customer
places an order, and it records the quantity for the order. In the
tblProductDetails I would like to run an update query to deduct however many
orders that customer places, from the total quantity for that product.

Can you please help me!! I really dont know how to perform an update query,I
can create one but im not sure about the CRITERIA or UPDATED TO columns.

Thanks
DAVE

 

[John Viescas]

Dave-

Maintaining a calculated value like this is tricky. There are basically two
ways to get the current quantity on hand:

1) Dynamically calculate it. See:
http://members.iinet.net.au/~allenbrowne/AppInventory.html

2) Perform the updates inside code that is protected with transactions. I
demonstrate this technique in Part III of Building Access Applications.
Caution: It takes several hundred lines of code to do it right.

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)