Update Table from Another Table

[chimworld]

Hello Friends

I have one Confusion

i have two Table like

Accounts
----------
AccID (Primary Key)
Balance
AccountName
ContactNo


Transaction
------------
TranID (Primary Key)
AccountID
TranDate
TranText
Credit
Debit

Relation
---------
Accounts.Accid - Transaction.AccountID



i want to Update Balance Field in Accounts Table from Transaction table

For Example:
Account.Balance = sum(Transaction.Credit-Transaction.Debit)

i tried so much time but i failed to solve so
Please HELP me to solve this Problem....


Thanks A Lot.

 

[boblarson]

1. You shouldn't store the balance. Calculate it at runtime using a query.

2. If you are wanting the table for doing reporting, you can use a query in place of the table so you won't need to store the calculated information.

This is all part of a properly normalized database structure.

 

[milehighfreak]

I think he's trying to make it update like that.

Create a seperate query to sum the credit/debit balance and report the account ID from Transactions.

Then create a new query and duplicate your Accounts table in it by including all fields, include the sum query and link them.

You should only have a single result in the sum query; you can simply replace the Balance column in your tables with the calculated, updated balance field from the sum query.

 

[boblarson]

I think he's trying to make it update like that.

Create a seperate query to sum the credit/debit balance and report the account ID from Transactions.

Then create a new query and duplicate your Accounts table in it by including all fields, include the sum query and link them.

You should only have a single result in the sum query; you can simply replace the Balance column in your tables with the calculated, updated balance field from the sum query.

There shold NOT be a balance field in the table and it should NOT be stored (UNLESS this is not a working database but instead is a data mart)

 

[milehighfreak]

There shold NOT be a balance field in the table and it should NOT be stored

replace the Balance column in your tables with the calculated, updated balance field in the query from the sum query.