Update query using two tables

Question

enter image description here

I want to update the ProductStock field in tblProduct when this query is run. The ProductStock should minus the Quantity field in tblOrderProduct. (ignore the * sign, I just wanted to see if multiplication worked). The user should be prompted to enter the OrderId beforehand

Thanks

Answer 1

You must specify the condition in a new column where you select the OrderID column. So, in the 3rd column select Table tblOrderProduct and Field OrderID and place [Enter Order ID] on the Criteria line of this column.

Replace the * by -. This will subtract the Quantity from the ProductStock.

Well, and you don't need the Quantity column. Remove it.

In the SQL view you will see

UPDATE tblProduct
INNER JOIN tblOrderProduct ON tblProduct.ProductID = tblOrderProduct.ProductID
SET tblProduct.ProductStock = [ProductStock]-[Quantity]
WHERE (((tblOrderProduct.OrderID)=[Enter Order ID]));