sql - How to update ms access database table using update and sum() function?

Question

I have Two tables in my access database

• table1(ID,productname,qunatity,remainder)
• table2(ID,productname,sales)

these tables are related together using "product name" ,How can I update"reminder" from table1 with the value of "quantity form first table - Sum(sales) from second table"

Answer 1

Because update queries in MS Access require updateable status, you cannot use a direct inner join on an aggregate query. Consider using the MS Access DSum() function:

UPDATE table1
SET table1.remainder = table1.quantity - 
    DSum("Sales", "table2", "ProductName='" & table1.ProductName & "'")