?
Solved

AR aging Report

Posted on 2007-10-03
12
Medium Priority
?
2,254 Views
Last Modified: 2012-06-27
I have a custid, due date and amount field.  It is in one table, araging.  I need to sum up the amounts by 30 days, 60 days, 90 days and 120 days by customer.  I need an ar aging report.  Can someone help?

format should be

cust id     current   30 days      60 days    90 days   120 days
123            $$$$      $$$$           $$$$          $$$$         $$$$

I'll need it in a query, please.  NO REPORTS.

0
Comment
Question by:jarjarbinks88
  • 5
  • 4
  • 3
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20007035
looks like a crosstab query is what you need.

can you post sample data
0
 

Author Comment

by:jarjarbinks88
ID: 20007132
cust id 1 due date 7/1/07 amount $110
cust id 2 due date 8/1/07 amount $120
cust id 1 due date 7/1/07 amount $200
cust id 3 due date 5/1/07 amount $110



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20007368
if duedate is more then 30 days where do you want it to show, say it is 35.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jarjarbinks88
ID: 20007490
30 day is the header and underneath the header should have the sum total amount due for the cust id.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20007541
<30 day is the header> i know that. if the past due amount is more than 30 days which column do you want it to be included ? in 60 Days?

sorry, never done any accounting works..
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20007587
SELECT cust_id,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN   0 AND  30 THEN amt ELSE 0 END) amt_due_30days,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN  31 AND  60 THEN amt ELSE 0 END) amt_due_60days,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN  61 AND  90 THEN amt ELSE 0 END) amt_due_60days,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN  91 AND 120 THEN amt ELSE 0 END) amt_due_120days,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN 121 AND 240 THEN amt ELSE 0 END) amt_due_240days,
SUM(CASE WHEN to_char(sysdate,'DDD') - to_char(due,'DDD') BETWEEN 241 AND 360 THEN amt ELSE 0 END) amt_due_360days
FROM cust
GROUP BY cust_id
0
 

Author Comment

by:jarjarbinks88
ID: 20007592
if the due date is 9/1/07 then that is 30 days old, but if it is 8/1/07 than is 60 days old, 7/1/07 is 90 days old and 6/1/07 is 120 days old.  sorry.  i guess it is from the now() will help calculate the days old.
0
 

Author Comment

by:jarjarbinks88
ID: 20007600
hi jinesh kamdar,

i am using access 2000, i believe the case when is only allowed in SQL, sorry.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20007633
Sorry, i didnt notice the MS Access zone. The query i gave would work in Oracle. And yes you're right, there is no CASE function in Access. Is there anything similar to the lines of Oracle's DECODE in MS Access?
0
 

Author Comment

by:jarjarbinks88
ID: 20007651
I know there is a iif but I can't seem to get it to work.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20007705
Try to frame your query based on the logic i used. If still not lucky, post your query.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20007727
try this
first create a query like this

SELECT tblArAging.custid, tblArAging.duedate, tblArAging.amount, DateDiff("d",[duedate],Date()) AS nDays, IIf([ndays]>90,"120 Days",IIf([ndays]>60,"90 Days",IIf([ndays]>30,"60 Days",IIf([nDays]>1,"30 Days","Current")))) AS xDays
FROM tblArAging
ORDER BY DateDiff("d",[duedate],Date());

save as qAging

then create a crosstab query

TRANSFORM Sum(qAging.amount) AS SumOfamount
SELECT qAging.custid, qAging.duedate, Sum(qAging.amount) AS [Total Of amount]
FROM qAging
GROUP BY qAging.custid, qAging.duedate
PIVOT qAging.xDays;


might need some tweaking

be back... off to a meeting


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

840 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