Error "Operation must use an updatable query" when update query. 1

[awinnn]

Hi,
I have an error, "Operation must use an updatable query" when trying to update the query. Any idea?
Is it possible to include sum() in the update query?
any idea? thanx in advance..

 

[lupins46]

An updateable query MUST be displaying only data held in tables.
The presence of ANY summarising function means that is not the case so you can't update.

There are several other situations leading to non-updateable queries, but it sounds like you understand your own problem and just want confirmation.

 

[BNPMike]

You can never use something like SUM because if you edit that figure, SQL has no idea how to map your change back to the underlying records. If you've got 2 records with the values 7 and 20, giving a sum of 27, and you change the sum to 26, what does that mean for the two records?

 

[ByteMyzer]

You can use the Domain Aggregate function DSum to do what you require. For example:

Table1 with two fields: Key, Value
Table2 with two fields: Key, Value

Objective: Update the value fields in Table2 with the sum of the Value fields for matching Key in Table1.

On the premise that Key is a Text field and Value is a Number field, here is the query:

UPDATE Table2 SET Table2.Value=
DSum("[Value]","Table1","[Key]='" & Table2.Key & "'");

 

[awinnn]

Hi all,
Sorry for late reply..
I've tested the DSum function and it works..
thanx again for the reply..