update table field with sum of values from another table field

[Rubydee73]

Hi I have a scenario that I need help with:

I have two tables, Transaction and Customer. The transaction table contains
a field called Amount. A customer can have more than one transaction.

The Customer table has a field called Balance. I would like for the balance
to be the sum of all of the customer's transaction amounts.

How do I have the Balance field in the customer table reflect the total of
the customer's transaction amounts?

 

[Rick B]

You don't.

As posted alllll the time, you don't store calculated values. You would
spend all your time trying to keep this number accurate. Everytime a new
invoice was added, the number would be wrong.

Instead, you would build a query to calculate the clients' balances and then
use that.

 

[Rubydee73]

Ok, how do I do what you suggest?

By the way - this is a very small (10 records) database that I have to
complete for a class.

 

[Rick B]

Here are some previous links that explain the process. You may need to copy
and paste thie links. Make sure the whole link is on one like. I got these
from Google Groups...


http://groups-beta.google.com/group...stomer+balance&rnum=22&hl=en#dbcf11f4d5ddabf1


http://groups-beta.google.com/group...stomer+balance&rnum=36&hl=en#0ce650f75c7544bf

http://groups-beta.google.com/group...ss+calculate+customer+balance&start=30&hl=en&



In short, you will have to create a query that sums all the transactions for
each given client and returns the client number and the sum.

 

[Rubydee73]

Thanks, for your advice Rick.

I have created the query that totals the amounts per customer. Here is the
SQL for it:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS [Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I have created the following update query:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance =
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I am getting a message that states that
the "operation must use an
updateable query". What am I doing wrong??

 

[Rubydee73]

Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS [Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance =
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that "operation
must use an updateable query".
What am I doing wrong?