• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 41
  • Last Modified:

Aged Analysis

Hi

Is there a way to match payments to actual bills in the age analysis, currently I’m using this formula on my query:
Due 30: IIF([DaysOverdue] Between 1 And 30,[AmountDue],Null)
It works well, but I want to be matching to specific invoices, you know some clients pay part payments.

Regards

Chris
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Asked:
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
  • 2
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That would depend on how you're storing Bills and Payments.

In one system I work with, each Bill/Invoice has a Due Date, and a Paid Date. If the Due Date has passed, but the Paid Date is Null, then that Invoice is considered past due. The Invoice record also stores the Original_Amount, and the Open_Amt (which I'm not a fan of, but it is what it is). In the case where a partial payment has been made, the Paid Date would remain Null.
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
I think whatever system one uses in terms of AR it has the same principles , the sales invoice/bills  are stored in the invoice /line detail tables while the payments are stored in the receipts/receipts line details tables.

To get the customer ledger we union the above tables as follows:

(1) tblInvoice(Parent) and tblLinedetails(Child)
(2) tblReceipts(Parent) and tblreceiptdetails(Child)

The invoices value minus the receipts value gives us the balance .

Now the issue here is how to match bills, if this was excel then that is simple but access it is something else!

Any idea

Regards

Chris
0
 
Gustav BrockCIOCommented:
To match the bills, record the invoice number a payment is related to in the details table - one record for each invoice.
Then it's easy to sum up and see if an invoice is fully or partial (or twice, as it happens) paid.

/gustav
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
If you mean referencing the invoice number when creating the receipts and then union the the receipts and bills then I will agree with you because at the moment that seams like the solution, but first confirm.

Regards

Chris
0
 
Gustav BrockCIOCommented:
Yes, though I would say join, not union.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now