Dealing with part payments of invoices

[stonward]

Hi Guys,

Got an odd(?) and awkward problem with an invoicing/statement producing database I'm working on.

A customer will likely have any number of outstanding (unpaid) invoices against their account. Most will pay the older ones based on the statement they are sent. But some will pay an 'odd' amount not related to any particular invoice number.

Has anyone come across this in Access/VBA? Can anyone give me pointers as to how to automatically (say) remove/pay the oldest invoices from the payment made?

Naturally I'm also having kittens with regard to Primary Key issues and table relationships related to this problem; i.e., I have had to alter the table relationships to account for Many Payments against One Invoice etc!

Thanks for your time.

Roy.

[NeoPa]

It depends on the flexibility requirements.

I would have a payment table and a separate payoff table. In most cases a payoff will match a payment, but there'll always be a record for each table. A form could control specifying the payoffs though. Payoff records would be linked to the Payment records on a One (Payment) to Many (Payoff) basis. A form would allow you to keep entering Payoff records until the Payment amount had been reached. The Payoff record would also have details of the invoice, which the Payment record would not have.

Does that all make sense?

[stonward]

Yes, and it's a solution I hadn't thought of. Thanks for your help: I'll let you know how it goes.

[NeoPa]

Always good to help if we can :-) Good luck with the project.