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

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 & "'")

Accepted Answer

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 table1SET table1.remainder = table1.quantity -     DSum("Sales", "table2", "ProductName='" & table1.ProductName & "'")

Advertisement

Answer