Updating records

[ZedtheHead]

I have a table which has the fields RATE and AMOUNT_OWED.

How do I get a query/function to traverse every record in the table and add the amount in RATE to the amount in AMOUNT_OWED for each record?

 

[neileg]

Use an update query.

However, I strongly suspect your database design is flawed as this is a poor way of calculating balances.

 

[ZedtheHead]

Thanks for replying.

What would be a better way of calculating balances, or where can I go to find some more info?

 

[neileg]

Do a search in this forum. Questions about stock and inventory often deal with the balance issue.

Basically, you should store transactions that increase or decrease your balance and calculate the balance by totalling the transactions. The problems with storing the balance include:
- No audit trail
- Not possible to caculate the balance at a previous date
- If you get data corruption, how do you know if the balance was calculated before or after the last transaction

Good luck, keep asking questions!