Credits to carry over next record

  • Thread starter Thread starter Brig
  • Start date Start date
B

Brig

Hello,

On an access 2000 database, we created a billing table.

This table has invoice information and the amount being charged.

InvoiceNo, BillTo, Address, City, State, Zip, AmountCharge, AmountRcvd,
AmountRcvdDate, PaymentAmount, PaymentAdjustment, PaymentInfo, PaymentDate,
Paid.

When we receive an underpayment, we want to keep that invoice open which
working the way we want it to work.

However, when we receive an overpayment, we would like to close that invoice
by marking the "Paid" control but to carry over to the next month any
credits to the "PaymentAdjustment".

Can you give me some advise or point me to the right direction on how to go
about this issue?

Thank you very much in advance.
 
Brig,

If only it were so simple... To begin with, your design is poor. Invoices
are one thing, payments are another, so they should really be in two
separate tables. Also, you should add a field to your Customers table to
hold current balance, which is where you should store the carry over
balance.

Now, how you handle partial or collective payment is a different story
altogether, one to which there is no unique answer; some (or all?) of the
world's leading ERP's will allow for several different ways, depending on
the user requirements. This is promarily a business decision, which then
affects the database design. One way is to use a third table to match
payements with invoices. If you are looking at FIFO settlement, you could
even put together some VB code to do the settlement upon receipt of a
payment.

Furthermore, you could add extra functionality for things like customer
statements, open items, ageing etc.

Hope this gave you an idea or two,
Nikos
 
Thank you very much for your advise. I guess I have to re-think this setup
then.

Thanks again.
 
Back
Top