You make a very good point, and if we were dealing with current balances only, I'd agree 100%. But how do I determine from the invoice details what the outstanding balance for 60-90 days old is? That's where my problem is.

Thanks,

-Michelle



>Michelle,
>
>The problem you are having is actually a violation of the third normal form for database design. The balance is a calculated value, adding up all the trips and subtracting all the payments. Maintaining this vlaue as a field in a table is where the problem starts. You have described the symptoms of the problem.
>
>The dynamic balance value is making it difficult to calculate the balance as iof a certain date. The reason you are haing difficulty is that you are trying to use the balance field in your calculations. You should produce your invoices from the details ONLY. Then you can limit the range of dates covered by the invoice and show correct totals on the invoice.
>
>If you need that balance field to display on a customer screen fine, but restrict it to only displaying on that screen, don't use that field anywhere else. In other places calculate the balance by adding up the details and subtracting the payments.
>
>What happens to your wonderfull invoicing system if a machine crashes after the invoice got in and before the customer balance got updated? The same for payments.
>
>By having data stored in two places, Balance, and in the details, you create the possibility for data inconsistency. Remove that possibility by only using the detail for all calculations, and updating the balance field from the detail whenever it gets out of synch.
>
>You may wonder, what if the balance is correct and the detail is wrong, the answer is so what? The accountant's job is to insure accuracy of the financial data, our job is to insure consistency. If all reports are consistent in their balances then the accountant can make a correcting entry to fix a financial error, but if our reports are not consistent in their balances, then no one can do anything to make them accurate.