How to adjust different amounts received from a customer?

[Athar Khan]

How to adjust receivings from Customer?

Sometimes a customer pays against a single invoice, sometimes for 2 or more, and sometimes he just pays in lump sum, and ignores the actual (total) amount of multiple invoices and says that we will adjust any remaining balance next time.



Now in this case, an accountant can not mark a single invoice as paid because the amount received is not adjustable to any invoice. For example, the total amount of :
Invoice No.1 = $110
Invoice No.2 = $210
Invoice No.3 = $50
Total = $370
but the customer pays just $300. How to adjust $300 and how to record remaining $70?

[ranman256]

the tPayment table would get the 3 records added.
but everytime one rec is added the tAccounts.Balance field would be reduced. (if you have a table that holds the balance)
IF not, then tAcctRcvd table would get these records also. The sum would to reduce the balance to zero.

[Micron]

I'm no accountant by any means, but I think there's more to it than just distributing receivables across invoices. To suggest applying 300 and marking #2 and #3 as paid, with the balance being applied to #1 probably wouldn't be advisable. If you can't coerce them into telling you which invoice(s) to apply the payments to, I can only envision applying the payments to the outstanding account balance. To do otherwise and mark #2 and #3 as paid makes no sense to me since the PO or job associated with either of them could still be open. Rather than make this your problem, perhaps you should insist on having them allocate the payments, else use your own discretion based on your knowledge of the business, to decide if you can close out an invoice or not.

[Athar Khan]

Thanks (ranman256 & Micron)
I think I can't mark any of three invoices as paid because of not matching the amount with any of these. I should create a separate table Payments to deal with. But I think I will have to put each invoice's total to that table manually and there should be a running balance in that table or query of that table.
Is there any way to put it the invoice total to the payment table programmatically?

[Micron]

In a new query's calculated fields, DSum the invoice amounts, and in another, DSum the payments table (WHERE CustomerID = ) in both cases. In a form control, subtract one from the other to get the balance. Join the two tables on CustomerID. Never (almost never) store calculated amounts in a table. I don't see a need for storing invoice totals in a payments table.

[Athar Khan]

Thanks Micron, please mark it as Solved for me

[Micron]

Can't. Only you, or maybe a moderator can do that.

[HiTechCoach]

Is there any way to put it the invoice total to the payment table programmatically?

No need. You calculate the invoice total as needed.

I think you may be missing a table.


Accounts Receivables: Applying Payments

A customer makes multiple separate purchases without making a payment.

Customer has multiple invoices outstanding.

You receive a payment from a customer.

If you are using the FIFO method to apply receipts, you pay off the oldest balance first by allocating part of the payment to the oldest invoice with an outstanding balance. Repeat this until the receipt has been fully allocated.

This requires two tables to handle the receipt of a payment and to allocate it to invoices.

1. Receipt: This is the detail about the payment received.
2. Invoice Payments: This will allocate part or all of the receipt to an invoice.

One Receipt record can generate multiple Invoice Payments records.

To see if an invoice is paid you compare the total for the invoice to the total payment applied to the invoice.
This method also makes handling payment terms giving a discount, like 2/10 Net 30, easy.

[Athar Khan]

Thank you HiTechCoach, you answered in details that makes sense. I will be thankful if you could share a sample DB having Invoice, Receipt and Payment tables.

[rpeare]

here is a simple example of what we're talking about:

Athar Khan.zip

Please not this is extremely simplified and is not the most efficient way to do this but it will work pretty well for small databases (like I think yours is)

[Athar Khan]

Thank you for your time and effort rpeare. I have downloaded database you sent but in frmInvoiceReceipt, I can't enter or see any value. I can just select the customer's name from combo box. Also when I select the customer A and click 'Apply Payments to Invoices' VB error number 94 (Invalid use of Null) appears.
I think I am missing something. And no relationship defined between tables, should I?

[rpeare]

oops I changed the customer select to a combo box from a listbox and forgot to update the code

In the ON CHANGE event of cboCustomer just put in

lstUnexpendedReceipt.Requery
lstUnpaidInvoices.Requery

you do not need to create relationships in a database, they are not necessary for a database to work.

[HiTechCoach]

@rpeare, Thanks or the example.

you do not need to create relationships in a database, they are not necessary for a database to work.

While that is technically true, I ALWAYS set up relations with the option to enforce referential integrity.

I find when building examples for other, especially beginners, it is best to create the relationships. It is a good visual aid.


@Athar Khan,

The key concept you should take away is the tables. I intentionally did not mention or show any example forms. In my accounting systems I have multiple forms that can add a record(s) to these table(s) based on the user's task/workflow.

Here is a screenshot from an Access 97 accounting system:

[Athar Khan]

Thanks rpeare and HiTechCoach for your support. I added these two lines to Change Event and it's solved and working now.

Is it possible to create queries and lookup fields by simply using wizards and relating queries/tables with each other without using VBA codes unlike you did in your sample (Athar Khan.zip)

[rpeare]

I'm not sure if I understand the question. If you are asking if you can do this type of function strictly with pre-built queries and macros, I don't honestly know, the queries are probably possible but I don't use macros so I'm not sure if you can recursively call a query to run, even if you could it would be a hell of a lot more complex than the 12 - 15 lines of code in the example.