Use Aggregate functions in a update query

[Jose.M.Huerta]

I want to pre-compute the sum of some values and store it on a table.
The query is:

UPDATE tblMachacas AS t1 SET Participacion = (SELECT Sum([Valor]) FROM
tblDefsTurno INNER JOIN tblTurnos ON tblDefsTurno.IdDefTurno =
tblTurnos.IdDefTurno
WHERE tblTurnos.IdMachaca = t1.IdMachaca);

In the table tblMachacas I have a field called Participacion that is
the sum of valor in all related registers of tblDefsTurno. But I get
the next error in access:

"La operación debe usar una consulta actualizable" -> The operation
must use an updatable query.

tblMachacas is a linked table and I can update it using:

UPDATE tblMachacas SET Participacion = 0 WHERE IdMachaca = 1;

How can I do it?

 

[Allen Browne]

Try a DSum():
UPDATE tblMachacas SET Participacion =
DSum("Valor", "Query1", "IdMachaca = " & [IdMachaca]);

Assumes Query1 has the 2 tables JOINed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to pre-compute the sum of some values and store it on a table.
The query is:

UPDATE tblMachacas AS t1 SET Participacion = (SELECT Sum([Valor]) FROM
tblDefsTurno INNER JOIN tblTurnos ON tblDefsTurno.IdDefTurno =
tblTurnos.IdDefTurno
WHERE tblTurnos.IdMachaca = t1.IdMachaca);

In the table tblMachacas I have a field called Participacion that is
the sum of valor in all related registers of tblDefsTurno. But I get
the next error in access:

"La operación debe usar una consulta actualizable" -> The operation
must use an updatable query.

tblMachacas is a linked table and I can update it using:

UPDATE tblMachacas SET Participacion = 0 WHERE IdMachaca = 1;

How can I do it?