How to Update Ms Access Database Table Using Update and Sum() Function

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

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

SQL/MS Access: Adding a automatically updating field in SQL table with sum of values from another table

You want a LEFT JOIN and NZ():

SELECT s.id, NZ(SUM(p.salary)) AS total
FROM staff as s LEFT JOIN
     payroll as p
     ON s.id = p.id
GROUP BY s.id;

You can also write this using a correlated subquery. This makes it easier to keep more columns from staff:

select s.*,
       (select nz(sum(p.salary))
        from payroll as p
        where p.id = s.id
       ) as total
from staff as s;

If you have a total column in staff you can update it using the last approach:

update staff
    set total = (select nz(sum(p.salary))
                 from payroll as p
                 where p.id = staff.id
                );

In SQL How is it possible to use the Sum Function in an Update

You can use DSum:

UPDATE Customers   
SET TotalP = DSum("Price","Products","CustomerID = " & CustomerID)

But it does raise the question as to why you are updating a table with a calculated value when the information can be obtained from a query.

SQL Update to the SUM of its joined values

How about this:

UPDATE p
SET p.extrasPrice = t.sumPrice
FROM BookingPitches AS p
INNER JOIN
    (
        SELECT PitchID, SUM(Price) sumPrice
        FROM BookingPitchExtras
        WHERE [required] = 1
        GROUP BY PitchID 
    ) t
    ON t.PitchID = p.ID
WHERE p.bookingID = 1

---

Related Topics