You can have a trigger that calculates the oustanding balance everytime a receipt is entered.

Check for triggers on the help!

>Hi,
>
>I couldn't find a section for design questions like this, so I've placed it here in chatter.
>
>I am working on a system where sales are entered, then when payments are received a receipt gets printed. The receipt and the invoice both show the balance due or outstanding.
>
>The tables I have are Sales and Sale Details and Payments and Payment Details.
>
>The Sales table has a field for Balance Due and the Payment Details table (related to the Sales table by Sale ID) also has the balance due stored in it.
>
>I have two problems with this design:
>
>1. Editing Payments causes problems with the balance due. For example, I make a sale for $600, then a payment of $400 is made against this sale. The Balance due on the Sale is put to $200 and the Balance Due on the Payments details record for this sale is put at $200. If another payment gets made of say $100, then the Sale's balance due is set to $100 and the payment details is set to $100. If I realise that a mistake was made on the first payment and it was actually $250 that should have been entered, changing the $200 payment to $250 causes all my balances to go off.
>
>Should I not let a payment be changed? Only let them add new payments?
>
>Or would a re-design help? Any suggestions?
>
>Or is the design good enough and it is just that I need to change the way my calculations are done when calculating the balance due?
>
>2. I dislike having calculated fields stored in the tables, I would much prefer to calculate the balance due on the fly. However doing it this way speeds up reporting and the user interface, so I will live with this.
>
>Thanks for any opinions.