Update With SUM

[Kenny Robb]

I have 2 tables.

First Table (MASTERDATA) Has all the master data for a customer and
one of the fields is Total Revenue

I have a second Table (SALES) which has various records each with an
Item Revenue field.

The Two tables are tied together with a customer ref no

What I want to be able to do is update the first table
MASTERDATA.TotalRevenue

with the sum of all revenue for that customer from the second table.

Tried a few things but somewhere in there my syntax or brain takes a
back seat and it doesn't work. Any ideas or helf would be appreciated.

I know this is wrong but here is the gist of what I want to do.

UPDATE MASTERDATA SET MASTERDATA.TotalRevenue = SUM(SALES.ItemRevenue)

and I want to go through the MASTERDATA table and do this for every
record.

Thanks
Kenny

 

[Michel Walsh]

Hi,


Use

UPDATE masterdata SET TotalRevenue = DSUM("ItemRevenue", "Sales")



and a WHERE clause if you want to restrict the update to only some of the
records.


Hoping it may help,
Vanderghast, Access MVP