"Best Way" to Build A Billing System

[Guest]

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

 

[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

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

 

[Guest]

Thank you Armen.

Bob

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

 

[PFC Sadr]

the 'best way' is to use SQL Server


Access MDB is for lamers and retards

 

[Doug Munich]

As long as you increase AmtsDue each year by the annual fee.

Doug