peraph Dsum..???? in query

[luca90]

Have 2 table:
table_total and table_single.

all tables have field Account, Amount and Cod

i need to summ all value in Amount in table_single, based the match/join Account>Account and Cod>Cod and update table_total amount from the realted summ of Amount from table table_single.

Googoling i have see Dsum, is correct? if yes how to via query?

Tks.

[FunkyDexter]

I believe DSum is available in Access if that's what you're using but I'm not sure it'll really give you what want. It would allow you to update one total row at a time but I don't theink it's format would allow you to do an update of the whole table.

I can't remember if access support updating from joins but if it does this should work:-

Update Tot
Set amount = Sum(Sing.Amount)
From table_tot Tot
Join table_single Sing
   on Tot.Account = Sing.Account
   and Tot.Cod = Sing.Cod
Group By Tot.Account, Tot.Cod

Failing that you could use a sub query:-

Update table_tot
set amount = 
   (Select Sum(amount)
   From table_single
   where table_tot.Account = table_single.Account
   and table_tot.Cod = table_single.cod)

I would question why you're maintaining a totals table though. That's a denormalisation and one you would generally want to avoid. Why not just calculate the totals from the single records as and when you need them?