Sum Update Query

[cazziewhelan]

Hi All,

I am trying to write a SQL statement query, that will calculate the SUM value in one table for a March period, and update that value to another table, here is what I have so far, but the compile error says Operation must use an updatable query.


UPDATE League_Totals SET League_Totals.March_Total = (SELECT SUM(Result_Value)
FROM Result_Details


WHERE LEAP_Date = "March_2014");

Any ideas what I am doing wrong?

[lfpm062010]

Try to use a DSUM function instead of sub-query.

[cazziewhelan]

Hi, thanks for your reply, I have managed to solove another issue with e DSUM function, however canot get it to work in the update query?

UPDATE League_Totals
SET League_Totals.March_Total = DSUM(Result_Details.Result_Value)
WHERE LEAP_Date = "March_2014";

Can you shed any light on how to structure an update query to include a DSUM function?
The DSUM is to calculate all Result_Value where LEAP_Date is March and Store_Number is 137 in Result_Details table and update this DSUM todal to March_Total in League_Results table.

It would be great if you could help me out on this one!

[lfpm062010]

DSum Syntax:

DSum("FIELDNAME", "TABLENAME", "CRITERIA")

Try this, I am not sure the "LEAP_Date" should go in the DSum "CRITERIA" area or in the UPDATE "WHERE" area.

UPDATE League_Totals
SET League_Totals.March_Total = DSUM("Result_Details.Result_Value", "Result_Details", "LEAP_Date = 'March_2014' ")
;

OR

UPDATE League_Totals
SET League_Totals.March_Total = DSUM("Result_Details.Result_Value", "Result_Details")
WHERE LEAP_Date = "March_2014";