SQL aggregation updates for some but not others

Question

I am running this query which should take the sum of an amount from a table and if it <= 0, update the status of a different table from Active to Deactive. The query updates some values but not others. I have isolated to one observation where there are 3 payments that total 0 where it does not work.(123456789) What could be happening here? I am using sql query in Microsoft Access. Thank you.

UPDATE tbl_MASTER INNER JOIN tbl_Payments ON tbl_MASTER.DeviceID = tbl_Payments.DeviceID SET tbl_MASTER.ActiveDeactive = "DeActive"
WHERE tbl_Payments.Amount=(SELECT SUM(tbl_Payments.Amount) <= 0 FROM tbl_Payments) AND tbl__MASTER = '123456789';

Answer 1

Currently, the subquery does not correlate specific IDs to outer query and also you specify <= 0 inside subquery's SELECT clause. Consider adjusting for IN clause with logic in a conditional HAVING and use table aliases to distinguish same named tables.

UPDATE tbl_MASTER AS m
INNER JOIN tbl_Payments AS p 
   ON m.DeviceID = p.DeviceID 
SET m.ActiveDeactive = 'DeActive'
WHERE sub_p.DeviceID IN (
    SELECT sub_p.DevideID 
    FROM tbl_Payments AS sub_p
    GROUP BY sub_p.DeviceID
    HAVING SUM(sub_p.Amount) <= 0
)