update query using a summed group query

[newnoviceuser]

Ill explain as best I can

I have a table called chart is has primary key called code and a field called balance
i have another table called transactions with fields code and amount.
i made a query called codetotals to sum transactions and group by code so I have a total of each code.
what I would like to do now is update chart.balance for each code with the sum in codetotals.

it keeps telling me must use an updaeable query. not sure what to do.

 

[dhookom]

Apparently you want to store a value that can be calculated. This normally is not generally a good practice but sometimes makes sense.

You can use DSum() to update Chart.

UPDATE Chart
SET  Balance = DSum("Amount","Transactions","Code=" & Code);

If the Code field is not numeric, try:

UPDATE Chart
SET  Balance = DSum("Amount","Transactions","Code=""" & Code & """");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newnoviceuser]

well not exactly what im looking for. see there are 70+ codes and diferent ones are used at different times. if I understand what you are saying I would have to do that command for each and every code every time. when i use a select query to bring up the code balance and sum of amount it only lists the codes and the total of each field that have expenses in them and all the balances as 0. what i need is a command that copys all the amounts in the codetotals field into the balance field. I know there has to be a way

 

[dhookom]

You stated

what I would like to do now is update chart.balance for each code with the sum in codetotals.

Make a new, blank mdb and import your two tables into this new, test mdb and try it. I believe each record in the Chart table will be updated with the sum of the Amount field from the Transaction table where the Code fields match.

If that is not what you want, let us know why.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[newnoviceuser]

yes it did work I tried the first time but after a closer look. i realized i did not put the [] around it and the query treated it like text. so it was searching for the code "code" now that I feel very stupid thanks very much for your help. it does what I need it to.