Update statement cannot use group by?

[lok1234]

Dear all,

I find that Access cannot use "group by" in the sub query. The following query will prompt up a message "Operation must use an updatable query." if being run:

update Summary as s inner join
(SELECT refno, sum(amount) as s_amount
FROM Details
WHERE Refno = '20090213001'
group by r.refno
) as d on s.refno=d.refno
set s.amount=d.s_amount

If I remove the "group by" and "sum(amount)" in the above statement, this update query is runable.

How to solve this problem or is there any workaround for this kind of SQL statement?

Many thanks

 

[CameronM]

You could call the aggregated query from the UPDATE query using DLookup.

UPDATE Summary AS s
SET s.amount = DLookup("SumOfAmount","qryDetailsSum","refno=" & s.refno & ")

Which uses a new query qryDetailsSum

SELECT d.refno, SUM(d.amount) AS SumOfamount
FROM Details d
GROUP BY d.refno