MS Access – Update Query Using Two Tables

ms accessquery

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

Best Answer

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]));

Related Solutions

MS Access Query – Find Person by First and Last Name

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer. In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)

Microsoft Access – Defining Destination for Append Query

If MainTable has more that one colum (I guess it does), then you need to specify it like that:

INSERT INTO MainTable(ColumnName)

Also you have to specify the columns in the SELECT STATEMENT:

SELECT Column1, Column2
FROM TABLE2....

Best Answer

Related Solutions

MS Access Query – Find Person by First and Last Name

Microsoft Access – Defining Destination for Append Query

Related Question