Account History Ledger

[SAC]

I'd like to be able to make an account billing and payments history ledger
like this:

Date Description Charge Payment
Balance
================================================
02/02/2009 Invoice No 16 $23.45 $00.00 $23.45

02/09/2009 Payment $00.00 $16.00 $
7.45

02/13/2009 Invoice No 34 $10.00 $00.00 $17.45

02/13/2009 Payment $00.00 $17.45
$00.00


How can I get the Balance column to behave this way? Should I store the
Balance in the transaction record or customer table or can I calculate it on
the fly?

Thanks for your help.

 

[Scott McDaniel]

I'd like to be able to make an account billing and payments history ledger
like this:

Date Description Charge Payment
Balance
================================================
02/02/2009 Invoice No 16 $23.45 $00.00 $23.45

02/09/2009 Payment $00.00 $16.00 $
7.45

02/13/2009 Invoice No 34 $10.00 $00.00 $17.45

02/13/2009 Payment $00.00 $17.45
$00.00


How can I get the Balance column to behave this way? Should I store the
Balance in the transaction record or customer table or can I calculate it on
the fly?

I think you should always calc it on the fly. What happens, for example, if I mistakenly enter $7.54 for the 02/09
payment, and don't catch that until I balance the account later in the month? Unless I have some mechanism in place to
update every other Balance, then all my subsequent balances would be off.

In most cases, you'd have a Transaction table (or whatever you want to call it):

tTransaction
---------------------------
lTransactionID [PK, Autonumber]
lOrderID [FK to relate to another table]
dTransDate
cTransAmount [+ for Credit, - for Debit]

From here you could calculate the Balance by a simple Sum(cTransAmount).

Thanks for your help.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com