1

I have a table like this: enter image description here


Now I'm trying to write in the column "SUMAMOUNT" of the table the sum of amount per "CODE" and "IBAN" but i can't reach this.

I'd want something like this:

enter image description here



I'm using this query but it doesn't work:

update tabella
set sumamount = (select sum(t2.amount)
                  from tabella as t2
                  where t2.code = tabella.code and t2.iban = tabella.iban
                 );

The precedent query gives me this result:


enter image description here

Can you help me? I'm using MS ACCESS. Thank you in advance!




EDIT: Screenshot of the error:

enter image description here



I can't even try to run it because he ask me to save it. When I try to save, access gives me this error.
Andrea Calabrò
  • 59
  • 1
  • 5

1 Answers1

2

Consider domain aggregate, DSum, which allows an updateable query. Below assumes code and iban are text types and therefore requires single quote enclosures.

UPDATE tabella t
SET t.sumamount = DSUM("amount", 
                       "tabella",  
                       "code = '" & t.code & "' AND iban = '" & t.iban & '");

(By the way, best practice in databases is to avoid saving calculations in tables. Save resources and simply run queries on data as needed.)

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Is `DSUM` only available within Access (like `Nz()`) - or does it work when using ODBC/OLEDB from other applications too? – Dai Jul 20 '20 at 04:50
  • 1
    Fantastic! Upvote for excellent solution. I had same issue and this answer solved my problem. Again, many thanks. – Harun24hr Jul 20 '20 at 05:35
  • @Dai, domain aggregates are available as part of Access' dialect in ODBC/OLEDB connections. – Parfait Jul 20 '20 at 14:21