30 60 90 Aged Detail on Report
in [Help]
|
Prev: VBA Editor Erratic Behaviour
Next: treeview question
From: BobbyDazzler on 9 Jul 2010 07:25 I currently produce month end statements for each of our account holding customers. Each statement has a brought forward amount, a list of invoices raised in that month, a list of payments made in that month and a due figure (being brought forward PLUS invoiced MINUS payments) What I want to do is have a 30 60 90+ aged detail on the report. Having as total mental block on this one!
From: Salad on 9 Jul 2010 11:00 BobbyDazzler wrote: > I currently produce month end statements for each of our account > holding customers. Each statement has a brought forward amount, a > list of invoices raised in that month, a list of payments made in that > month and a due figure (being brought forward PLUS invoiced MINUS > payments) > > What I want to do is have a 30 60 90+ aged detail on the report. > Having as total mental block on this one! If you have a list of payments are they associated with an invoice? For example, if you have 3 invoices for $1000 and you received a payment of $600, do you have a balance due on the 3? If so, you can calc the aging by the invoice date. If you don't have a baldue for each invoice, what can you compare the aging to?
From: BobbyDazzler on 9 Jul 2010 11:43 Invoice payments are not linked to invoices, many of our customers pay odd amounts which do not calculate to the invoices. I did think of paying each invoice and carrying forward a balance but thought that it was best practice not to store a calculated value in Access?
From: Salad on 9 Jul 2010 13:27 BobbyDazzler wrote: > Invoice payments are not linked to invoices, many of our customers pay > odd amounts which do not calculate to the invoices. I did think of > paying each invoice and carrying forward a balance but thought that it > was best practice not to store a calculated value in Access? If I have invoice #1 for $1000 and invoice #2 for $1000 and a payment for $2000 but the payment is not applyed to an invoice then how would I know if invoice 1 or 2 has been paid? How would you know the balance due of an invoice? How would you be able to age an invoice? I don't see how anyone here can assist with your problem. If you sum up the invoice total and sum up the payment total and you subtract the two sums then you know how much they owe or you own them. That's about it. When you get a check does the customer provide a list of invoices the check is being applied to? If someone had a question about the balance, do you print out a list of all invoices they've ever had and all of the payments they've ever made and let them sort it out? IMO, you have a problem. I suppose you could correct it by adding a baldue field, filling that with the invoice amount, summing up all payments ever received by a customer, and going for earliest invoice to latest invoice, if the invoice amt is less than total payments, baldue is 0 for that record and decrement the total payment amount from the invoice amount, and skip through the list until eof for that customer or invoice amount exceeds total payments. Then go to the next customer. If a customer had 3 invoices; 1,2,3 but they made payments on 1 and 3 but you calced on 1,2, then you/they may have a difficult time reconciling it if there's a dispute somewhere along the line. My suggestion, take it up with the boss. Explain the dilemma.
From: BobbyDazzler on 9 Jul 2010 18:36
Thanks for replying again, Salad. I accept what you are saying. Using your suggestion, what would I do if the customer had 3 outstanding invoices (100, 60 and 40) and paid 150? Would I need to keep a track of the payment received and how it was allocated? How would I set this up? |