Automatically Update a balnce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a database to update a field in a table that contains the
monitary balance of an account. When a person makes a payment I would like
it to subtract the payment amount from the current balance and post the new
balance. If anyone can help, please email me at one of two locations,
(e-mail address removed)
or
(e-mail address removed)

Thanks,
 
David, you post a question here, and it is answered here, not by email.

Updating a balance with each transaction is not the right way to design a
database. It breaks one of the most basic rules of relational data design,
i.e. don't store dependent data.

It is not just a matter of using the AfterInsert event to make the change.
There are just too many things that can go wrong. Have you thought about how
you will handle the cases where a user:
- changes the amount in an existing record?
- changes the account in an existing record?
- deletes an existing record?
- deletes several records at once (e.g. in a continuous form/datasheet)?
- starts a delete, but cancels it?

Instead, you need a design that calculates the balance when needed. Or you
need a temporary table (populated with an Append query) to print (and keep a
record of) a statement at a point in time. Or you need a double-entry
accounting system where periods are closed off and balances carried forward.
 
Back
Top