One Payment for Multiple Invoices

[Llobid]

I have a task I need to get done and need some guidance as to how/where to start. I have a database on which Invoices are created. I need to create an “Accounts Receivable Ledger” which contains for each customer a list of his invoice numbers and totals, and a list of payments (credits) and the corresponding check number. I would also like for the ledger to carry a balance due.

I have created a “Payments” table that contains the fields: Count (AutoNumber), Customer ID (Number), Payment Date (Date/Time), Check Number (Text), and Payment Amount (Currency). I then tried to create a query that would combine the data from my invoice query and my payment query. The problem is that since I have linked the two via Customer ID, I get repetitive data; it puts the payments down as if they correspond to particular invoices. The customer may write one check that covers multiple invoices, so I need it to list all of the invoices as well as all of the payments for a customer.

I have already created an input form for the “Payments” and already have an input form for the “Invoices”. I just need to have some way for the customer to be able to see all of the charges and payments for a customer summarized on an input form … then a report.

Thanks for any guidance you can give me.

PS: I am not adept at VBS coding, so if possible, I need to be able to do it using the other common Access tools. I also can't seem to find a template in the program that has an Accounts Receivable portion.

 

[HiTechCoach]

First: Avoid using reserved words for field names. Naming a field Count will probably cause you lots of headaches in the future if not already.

The way I handle the payment allocation in my accounting system is to use a third junction table that is used to allocation the payment to each invoice that it is paying.

 

[Llobid]

Thanks Boyd, but the problem is that there is NOT a one-to-one correspondence between payments and invoices. However, I have come up with a solution that I think will work for this instance. I am going to simply use the current "Invoice" form and put a designation "Payment" in the details control, then use a negative number. Then I will be able to create a report that shows all of the "Invoices" (Bills and Payments).

 

[HiTechCoach]

... but the problem is that there is NOT a one-to-one correspondence between payments and invoices.

Exactly. That is why I suggested that you use the junction table to create a many-to-many relationship. Getting the balance due is very simple. Tihs method also easily handles over payments.

 

[DCrake]

In the past I wrote a similar thing, however I did it in code, which you state you are unfamiliar with, anyway, that said this is how I did it.

Assumptions:
The customer is paying invoices in FIFO method, ie the oldest invoice is paid first.

Firstly I created a query for the selected customer with invoices in oldest to newest order

Lets say there were 5 invoices

1 = £300
2 = £100
3 = £150
4 = £300
5 = £60

The cusomer then send me a cheque for £650 to pay off part of his account balance

Using a loop through the recordset and using a reducing balance I visited each invoice and said...

Is there enough balance remaining to pay of this invoice in full? IF so mark the invoice as paid in full otherwise reduce the amount outstanding by the balance remaining. So..

Balance = £650

Loop 1
Inv 1 = £300
? is Balance > invoice amount.... Yes
Action :
a) flag as paid in full
b) reduce balance by invoice amount

Balance = Balance - Invoice amount (650-300) = 350

? have I any money left
Yes - perform another loop
No - exit loop

Loop 2
? is Balance > invoice amount.... Yes
Action :
a) flag as paid in full
b) reduce balance by invoice amount

Balance = Balance - Invoice amount (350-100) = 250

? have I any money left
Yes - perform another loop
No - exit loop
Loop 3
? is Balance > invoice amount.... Yes
Action :
a) flag as paid in full
b) reduce balance by invoice amount

Balance = Balance - Invoice amount (250-150) = 100

? have I any money left
Yes - perform another loop
No - exit loop
Loop 4
? is Balance > invoice amount.... No
Action :
a) flag as part paid
b) Reduce amount outstanding by current balance
£300 - 150 = £200 outstanding
c) reduce running balance to zero

Balance = 0
? have I any money left
Yes - perform another loop
No - exit loop