"Best Way" to Build A Billing System

B

Bob Barnes

I have a 1-to-many Relationship for Yearly Payments on Annual Memberships.

Is the "Best Way" to calculate any Accounts Receivable (AR) to add the
individual payments in the Group Footer for the Member and calculate the
(AmtsDue - Sum(Paymemts)) = AR???

TIA - Bob
 
A

Armen Stein

I have a 1-to-many Relationship for Yearly Payments on Annual Memberships.

Is the "Best Way" to calculate any Accounts Receivable (AR) to add the
individual payments in the Group Footer for the Member and calculate the
(AmtsDue - Sum(Paymemts)) = AR???

TIA - Bob
Click to expand...

The most flexible way is to treat each invoice and payment as a
transaction. When a member owes a new dues amount, that's a
transaction with a positive amount. When they make a payment, that's
a transaction with a negative amount. Sum up all the transactions for
a member and you have the current AR balance. This has the advantage
of keeping your whole charges and payments history in one place.

To make it even fancier, set up a TransactionType lookup table. In
that table, make a field called TransactionTypeBalanceEffect, which
holds either a +1 (for transactions that increase the balance, like
dues charges) or -1 (for transactions that decrease the balance, like
payments or refunds).

Then each Transaction can have a normal positive dollar amount. When
you join in the TransactionType table, you just multiply the
TransactionAmount by the BalanceEffect (+1 or -1) to flip the sign as
necessary.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
B

Bob Barnes

Thank you Armen.

Bob

Armen Stein said:
The most flexible way is to treat each invoice and payment as a
transaction. When a member owes a new dues amount, that's a
transaction with a positive amount. When they make a payment, that's
a transaction with a negative amount. Sum up all the transactions for
a member and you have the current AR balance. This has the advantage
of keeping your whole charges and payments history in one place.

To make it even fancier, set up a TransactionType lookup table. In
that table, make a field called TransactionTypeBalanceEffect, which
holds either a +1 (for transactions that increase the balance, like
dues charges) or -1 (for transactions that decrease the balance, like
payments or refunds).

Then each Transaction can have a normal positive dollar amount. When
you join in the TransactionType table, you just multiply the
TransactionAmount by the BalanceEffect (+1 or -1) to flip the sign as
necessary.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...
 
Top