[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to create an AR Aging Query

Posted on 2005-05-14
16
Medium Priority
?
2,648 Views
Last Modified: 2012-06-22
I have a table that records all the payments made by customers and another table that has billing details of all the bills created for the customers. (new bills added every month for the customers)
I need to create an aging query to show outstanding balances in "buckets" like current, > 30 days, > 60 days, >90 days and all over 120 days. The basic theory as I think should be is to "Find out the sum of all payments made, and adjust the payments on FIFO basis against the various bills & find the outstanding in various buckets."

Problem: What's the best way to accomplish this? I've tried creating various types of queries, and can't get any of them to work. Someone suggested me to use a combination of partition function & crosstab query, but I was unable to implement them to get a workable solution. I would like to view the resultant query in the format
CustId, CustName, Dues in Bucket(Current, >30, >60, >90, >120), TotalDues

Once I get the data in this format I will be using it for further analysis.
Any suggestions would be greatly appreciated along with the appropriate syntax/sql to accomplish the task. I'm sure this is simple for the experts here, but I can't seem to get a handle on a proper approach.

Structure of Payments Table –[Revenue Master]
CustId –[BAN Number]
BillId–[Invoice Number]
Amount –[Payments]
PmtDate –[Date of Collection]

Structure of Bill Table –[Billing Master]
CustId –[BAN Number]
BillId –[Invoice Number]
Amount–[Monthly Due]
BillDt –[Invoice Date]
BillDueDt–[Due Date]

Names in the [] brackets denote the actual table/field names.
I have not included the structure of the Customer table here but a truncated version is present in the sample DB.

A sample stripped off database with some records is posted on the URL
http://www.geocities.com/neeraj_chow/Sample_Age_DB.mdb

Any help/guidance to resolve the problem will be highly appreciated.
0
Comment
Question by:neerajc
  • 9
  • 6
16 Comments
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14002013
Use the following SQL for ur query :

SELECT [Billing Master].[Invoice Number], [Billing Master].[BAN Number], [Billing Master].[Invoice Date], [Billing Master].[Due Date], [Revenue Master].Payments, [Revenue Master].[Date of Collection], [Billing Master].[Monthly Due]
FROM [Customer Master] INNER JOIN ([Billing Master] INNER JOIN [Revenue Master] ON [Billing Master].[Invoice Number] = [Revenue Master].[Invoice Number]) ON ([Customer Master].[BAN Number] = [Revenue Master].[BAN Number]) AND ([Customer Master].[BAN Number] = [Billing Master].[BAN Number]);

In report paste the following functions for 20/60/90 and 120 days as follows :

Function agedetails0()
   
   If Now() - Me.invoicedate > 0 And Now() - Me.invoicedate <= 30 Then
        agedetails0 = Me.netamount - IIf(IsNull(Me.rvinvamt), 0, Me.rvinvamt)
    End If
   
End Function
*************
Function agedetails30()
   
    If Now() - Me.invoicedate > 30 And Now() - Me.invoicedate <= 60 Then
        agedetails30 = Me.netamount - IIf(IsNull(Me.rvinvamt), 0, Me.rvinvamt)
    End If
   
End Function
**************
Function agedetails60()
    If Now() - Me.invoicedate > 60 And Now() - Me.invoicedate <= 90 Then
        agedetails60 = Me.netamount - IIf(IsNull(Me.rvinvamt), 0, Me.rvinvamt)
    End If
End Function
**************
Function agedetails90()
    If Now() - Me.invoicedate > 90 And Now() - Me.invoicedate <= 120 Then
        agedetails90 = Me.netamount - IIf(IsNull(Me.rvinvamt), 0, Me.rvinvamt)
    End If
End Function
*********
Function agedetails120()
    If Now() - Me.invoicedate > 120 Then
        agedetails120 = Me.netamount - IIf(IsNull(Me.rvinvamt), 0, Me.rvinvamt)
    End If
End Function
**********

In the above example u can replace invoicedate with duedate

Take on report  0-30 days   30-60 days        60-90 days        90-120 days  >120 days

and in control source of each put as follows :

age0  =agedetails0()

age30 = =agedetails30()

age60 = =agedetails60()

age90 = =agedetails90()

age120 = =agedetails120()


Cheers

Aziz
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14002020
Sorry replace   In report paste the following functions for 20/60/90 and 120 days as follows :


with

In report paste the following functions for 30/60/90 and 120 days as follows :

Aziz
0
 

Author Comment

by:neerajc
ID: 14008259
Pardon me for my remark, but I was looking for a query based solution and not a report, because as I had said earlier that I would be conducting further analysis on it once I get the aging query in a right shape. Please guide how can your solution be converted into the form of a query based solution.

I am not that much of an expert, but yes I do try to learn.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14008466
No problem .... u can use this following SQL statement which will serve as CrossTab for Aging 0-30/30-60/60-90/90-120 and >120 Days :

SELECT Query1.[Invoice Number], Query1.[BAN Number], Query1.[Invoice Date], Query1.[Due Date], Query1.[Monthly Due], Query1.[Date of Collection], Query1.Payments, IIf(Now()-[Due Date]<=30,[Monthly Due]-[Payments],0) AS [0-30 Days], IIf(Now()-[Due Date]>30 And Now()-[Due Date]<=60,[Monthly Due]-[Payments],0) AS [30-60 Days], IIf(Now()-[Due Date]>60 And Now()-[Due Date]<=90,[Monthly Due]-[Payments],0) AS [60-90 Days], IIf(Now()-[Due Date]>90 And Now()-[Due Date]<=120,[Monthly Due]-[Payments],0) AS [90-120 Days], IIf(Now()-[Due Date]>120,[Monthly Due]-[Payments],0) AS [120 Days OR More]
FROM Query1;

Aziz
0
 

Author Comment

by:neerajc
ID: 14008909
I assumed that the first query mentioned is named query1 and the second query is based on query1 and then created grouped by custid - Query3 based on query2, to get a customerwise aging information.
But I see some possible problems with the solution, which needs rectification.
One is that in cases where bills are generated but for which we have no payments recieved so far do not appear in the query results.
In the DB we have mapped payments against specific invoices, but which is not always accurate, that is why I had thought of the FIFO rule of adjustments as described in my ques. Due to this assumption, what happens is that we can have gaps in aging results eg. in some case, we have outstanding in 0-30, 31-60, and then in 90-120 bucket, which cant happen because outstanding would be in continous buckets. Allow me to illustrtate it by an example the FIFO rule of payment adjustments:
Lets say 6 bills have been generated for a Customer X as below:
Date | Amount
----------------------
01-12-2004 | 540
01-01-2005 | 765
01-02-2005 | 1250
01-03-2005 | 3000
01-04-2005 | 1375
01-05-2005 | 1000

so the Total Billed amount is 7930

and lets say he has made payments as below:

29-12-2004 | 400
10-01-2005 | 1000
27-02-2005 | 1100
01-04-2005 | 2300

Total payments made are 4800

Payments would be adjusted against the bills on FIFO basis as illustrated below :

Date | Amount | FIFO Adjustments | Dues
01-12-2004 | 540 | 540 | 0
01-01-2005 | 765 | 765 | 0
01-02-2005 | 1250 | 1250 | 0
01-03-2005 | 3000 | 2245 | 755
01-04-2005 | 1375 | 0 | 1375
01-05-2005 | 1000 | 0 | 1000

So when I run the query I would know that the outstandings of the customers are due from 1 Mar. and the results should be displayed as

CustId      TotBilled            TotPmts            >0      0-30      >60      >90      TotalDues
00001      6930            4800            1000      1375      755      0      3130

for all the customers and in cases where payments are in execss of billed amount, all the buckets would show a zero amount .

My thinking is that the problem requires a combination of VBA code & query. Please advise.
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14008964
There is one problem. The bills should appear in AR aging individually to keep track for each and every invoice.

regarding payments u need to modify ur data entry form to include one flag say InvStat as YES/NO. for example :

Cust1 has invoice for $ 5000 and he paid as follows :

01/05/2005 ............. $ 1000
05/05/2005 ............. $ 3000
10/05/2005 ............. $ 1000

At the time of current entry the code should replace all previous invoices with NO and the current if leaves balance then should update with YES and if settled completely then update with NO.

In this case the aging will be as on current date basis and not on datewise basis. If it suits you then ok otherwise u need another technique.

Aziz




0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14008972
u can also do it in Query rather than efforting in payments entry as follows :

SELECT Query1.[Invoice Number], Query1.[BAN Number], Query1.[Invoice Date], Query1.[Due Date], Query1.[Monthly Due], Query1.[Date of Collection], Query1.Payments, IIf(Now()-[Due Date]<=30,[Monthly Due]-[Payments],0) AS [0-30 Days], IIf(Now()-[Due Date]>30 And Now()-[Due Date]<=60,[Monthly Due]-[Payments],0) AS [30-60 Days], IIf(Now()-[Due Date]>60 And Now()-[Due Date]<=90,[Monthly Due]-[Payments],0) AS [60-90 Days], IIf(Now()-[Due Date]>90 And Now()-[Due Date]<=120,[Monthly Due]-[Payments],0) AS [90-120 Days], IIf(Now()-[Due Date]>120,[Monthly Due]-[Payments],0) AS [> 120 Days], IIf([Monthly Due]-[Payments]>0,"YES","NO") AS InvStat
FROM Query1
WHERE (((Query1.[Invoice Number])=[invoice number]) AND ((IIf([Monthly Due]-[Payments]>0,"YES","NO"))="YES"));

Aziz
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14008979
This query will bring only the invoice which has not been completely settled and you can derive it at any given date. instead of NOW().
0
 

Author Comment

by:neerajc
ID: 14016556
As I mentioned earlier in cases where bills are generated but for which we have no payments recieved so far do not appear in the query results. This problem needs to be addressed. Secondly it can happen that customer makes multiple payments against a single invoice or makes payments for multiple bills in one go. Due to this in the resulting join in query1, billed amount will show a increase due to the fact that multiple invoice entry will be shown against each payments. How to remove this extra billed amount.
I was thinking that a solution would be needed based on the sequence of activities as described in the example in my previous remark.
Do you think I am thinking on a right track or is there some loopholes in my approach.
0
 
LVL 12

Accepted Solution

by:
Sayedaziz earned 1000 total points
ID: 14016664
Modified SQL view for Query1
~~~~~~~~~~~~~~~~~~~

SELECT [Billing Master].[Invoice Number], [Billing Master].[BAN Number], [Billing Master].[Invoice Date], [Billing Master].[Due Date], IIf(IsNull([Payments]),0,[Payments]) AS Pymts, [Revenue Master].[Date of Collection], [Billing Master].[Monthly Due]
FROM [Billing Master] LEFT JOIN [Revenue Master] ON [Billing Master].[Invoice Number] = [Revenue Master].[Invoice Number]
ORDER BY [Billing Master].[Invoice Number];

***********************

Accordingly Modified SQL View for Query2
~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT Query1.[Invoice Number], Query1.[BAN Number], Query1.[Invoice Date], Query1.[Due Date], Query1.[Monthly Due], Query1.[Date of Collection], Query1.Pymts, IIf(Now()-[Due Date]<=30,[Monthly Due]-[Pymts],0) AS [0-30 Days], IIf(Now()-[Due Date]>30 And Now()-[Due Date]<=60,[Monthly Due]-[Pymts],0) AS [30-60 Days], IIf(Now()-[Due Date]>60 And Now()-[Due Date]<=90,[Monthly Due]-[Pymts],0) AS [60-90 Days], IIf(Now()-[Due Date]>90 And Now()-[Due Date]<=120,[Monthly Due]-[Pymts],0) AS [90-120 Days], IIf(Now()-[Due Date]>120,[Monthly Due]-[Pymts],0) AS [> 120 Days], IIf([Monthly Due]-[Pymts]>0,"YES","NO") AS InvStat
FROM Query1
WHERE (((IIf([Monthly Due]-[Pymts]>0,"YES","NO"))="YES"))
ORDER BY Query1.[Invoice Number];
***************************

This will show the invoices for which no payments has been received for example :

invoice # 280004056371 for Monthly Due $ 36,000.00

if u have any further explanation/problem ... i m waiing to dig further.

Aziz
0
 

Author Comment

by:neerajc
ID: 14034383
Sorry for the delay, as the net connection was not working.
The solution assumes that payments are mapped properly against their respective invoices, which is hardly the case as explained in my previous remarks. Moreover if in some cases payments are made more than the billed amount the negative amount is shown in the ageing query in that particular bucket. The solution prescribed is satisfactory in the sense that it gives a rough estimates in aging.
But can a solution be derived on lines of my above quoted example ??
It would be great if an answer could be provided on the lines of the example.
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14035173
See neerajc,

My assumption af aging is as follows :

< Payments are received in advance as well as against particular invoice. >

u have not prepared your Payment entry form OR its not incleded in the sample DB

What i prefer is to make a Payment Entry Form and a Payment Sub Form whose structure would be as follows :

                                              Payment Main Form
__________________________________________________________________

Customer ID :  <Combo Box>
Receipt ID    :   <Text Box Auto Numbering>   Receipt Date :  <Text Box>
Receipt Amount : <Payment ReceivedTextBox>

and other fields as required

                                           Payment Sub Form ( data Sheet View )

<InvNoCombo>   <Monthly DueTextBox>  <PaymentAdjustedTextBox>**

** This will help you to derive aging accurately.
if payment is more than Monthly Due then allow to select other Inv No otherwise Move focus to Main Form Control.

________________________________________________________________
                                           Payment Main Form

Total Adjusted        : <Total Of <PaymentAdjustedTextBox> >
Unadjusted Amount : <Payment ReceivedTextBox> - <Total Of
                                                                               PaymentAdjustedTextBox> >
These above 2 fields will be locked so that user can not change this value and these will be Unbound means recorded nowhere.

I m using the same concept in my database which is an mde and located in my website:

http://www.geocities.com/aziz_abroad

In LogIn Form select userid as guest and password also guest.Take a view of the concept and inform me if u like complete coding for the Reciept Voucher Form.

Aziz


 

0
 

Author Comment

by:neerajc
ID: 14043353
I fully agree with what you are saying 'coz that's the way the thing should have been designed in the first place.
I have not designed the system here, its just that I have inherited it. This whole thing is analogus to the saying that "One cannot choose where to be born".
I will try to get your suggestions implemented in the future. But for the while I need to have a correct aging query on the lines of the example mentioned above. Can it be done ???

0
 
LVL 5

Expert Comment

by:BevinManian
ID: 14058187
I can work out a solution for you.  But before that you need to see if the below format is OK with you or not.

Cust.  TotBalance   >0   0-30   30-60  60-90  >90  OpenCredit

where,
TotBalance is [>0] + [0-30] + [30-60] + [60-90] + [>90] - OpenCredit

This is the "Best Practices" method used in most sophisticated (even ERP) apps for AR.  All payments are attributed to an Invoice.  If a payment is "On Account" or cannot be attributed to a single invoice, its falls into OpenCredit.

On the other hand, you can use solution 2.
Cust.  TotBalance   >0   0-30   30-60  60-90  >90

This will use strictly the FIFO method as mentioned somewhere in this thread.  Here the payments knock off the older balances regardless of the invoice number.

And also, I understand you want to see all this info in a single query.

Can be done....

Just let me know which exact methodology you would like to use.

Bev
0
 

Author Comment

by:neerajc
ID: 14100432
Thanks SayedAziz for the insight given to me. You were great. However BevinManian has provided the solution that I was looking for at http://www.experts-exchange.com/Databases/MS_Access/Q_21433075.html
and Thanks once again for the pain taken and help extended to me
Regards
Neeraj
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14100465
Thx neerajc. hope to meet u again in EE

Aziz
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question