CODEWITHSUNDEEP
sql
wisam ali
wisam ali

Reputation: 33

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

I have Two tables in my access database

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"

Upvotes: 2

Views: 8924

Answers (3)

Parfait
Parfait

Reputation: 107687

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

Upvotes: 3

boxama
boxama

Reputation: 58

When you say that it's linked by the productname field please tell me in table 1 that is a foreign key. Since you already have an ID in table 2 there's no reason not to use that ID as the foreign key in table 1.

If you were to do that the update would be as simple as this:

update table1 
set table1.quantity = table1.quantity - SUM( table2.sales ) 
from table1
inner join table2 on table1.productID = table2.productID
where table1.productID = 1;

Upvotes: 0

Rahul
Rahul

Reputation: 77896

Perform an update join by getting the SUM() first like

UPDATE a 
SET    a.remainder = x.SaleTotal
FROM   table1 a 
       INNER JOIN (SELECT productname, SUM(sales) AS SaleTotal 
                   FROM TABLE2 GROUP BY productname) x 
       ON a.productname = x.productname;

Upvotes: 0

Related Questions