How to Make field in one Table equal to the calculated value of duplicates in another? (See inside)

[dragon232]

I have two tables, call them Table1 and Table2.

Table1 has 10 fields. The first two (the only two which matter to this problem) are
CNP: Primary Key, Short Text field
Money: Number field

Table2 has 3 fields.
ID: Primary Key
CNP: Short Text
Money: Number field

I want the value of Table1.Money corresponding to each record by CNP to be equal the sum of all the Table2.Money values for the said CNP value. So say Table1 has 3 records and Table2 has 5 records as follows:

Table2
1; 20; 200


2; 21; 300
3; 20; 300
4; 20; 100
5; 21; 500

Table1
20; ?
21; ?
22; ?

I want the "?" in Table1 to equal (200+300+100) for CNP 20, (300+500) for CNP 21, and 0 for CNP 22 (as there are no records in Table2 for it).

The question is how can I achieve this? One way I can envisage to do this is (1) to create a query - call it Query1 - which takes Table2 and sums up all duplicates, such that Query1 prints:
20; 600
21; 800
(2) Then I relate this Query1 with Table1 based on the CNP field in a 1-To-1 Relationship, and (3) using an update Query, call it Query 2, update the Money Field of Table1 with the Money Field in Query1 IF Query1.CNP = Table1.CNP and otherwise update the Money Field in Table1 with 0.

The issue is that even if this is the right way to do this, I'm not sure how to execute step (1) of the plan. Is there another way to do this? If there isn't or if the way I proposed is the optimal way to achieve this goal, how can I create a query which takes a Table, and sums all the duplicates, such that N records having CNP value of X are transformed into 1 record having CNP value X? Thanks!

[CJ_London]

left join table1 to table2

then group by table1 cnp and sum table2 money

something like

select table1.cnp, sum(table2.money)
from table1 left join table2 on table1.cnp=table2.cnp
group by table1.cnp

[dragon232]

left join table1 to table2

then group by table1 cnp and sum table2 money

something like

select table1.cnp, sum(table2.money)
from table1 left join table2 on table1.cnp=table2.cnp
group by table1.cnp

Okay I did that to get my Query1 in my step (1) done. Then when I make an Update query to update Table1.Money based on Query1.SumOfMoney it gives me an error: "Operation must use an updatable query". Also doing this leaves CNP 22 in Query1 as null instead of 0 it seems. Seems that the error is because Query1 is read-only.

[dragon232]

Okay I managed to solve one difficulty. I make Query1 into a "Make Table" Query, and then use the created table to update Table1, which works. However the problem remains the null fields. I don't want them to be null, they have to be 0. I could make another Query on top of Query 1 which updates the field to 0 if the Sum is Null. But that's 2 queries. Is there a way to do it all in one query?

[CJ_London]

you didn't say it was for an update query - update queries don't work with group by queries.

I

don't want them to be null, they have to be 0

use the nz function

[dragon232]

I use the nz function

Yes but that requires another query on top doesn't it? Like I'll have the Query1 which you suggested, and then I have a Query2 which takes the fields of Query1 and does:
Money: Nz([SumOfMoney], 0)
Then I make this Query2 into the MakeTable query, and then update based on that table. I've done this (actually I used IIf and IsNull functions to do the same thing), which works, but I'm curious if there is anyway to shorten the operations of Query1 and Query2 all within one query?

[CJ_London]

if you want to do it in one query, use the dsum function

[dragon232]

if you want to do it in one query, use the dsum function

Okay thanks!