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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.