AR aging Report

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.

jarjarbinks88Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
looks like a crosstab query is what you need.

can you post sample data
0
 
jarjarbinks88Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Rey Obrero (Capricorn1)Commented:
if duedate is more then 30 days where do you want it to show, say it is 35.
0
 
jarjarbinks88Author Commented:
30 day is the header and underneath the header should have the sum total amount due for the cust id.
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
Jinesh KamdarCommented:
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
 
jarjarbinks88Author Commented:
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
 
jarjarbinks88Author Commented:
hi jinesh kamdar,

i am using access 2000, i believe the case when is only allowed in SQL, sorry.
0
 
Jinesh KamdarCommented:
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
 
jarjarbinks88Author Commented:
I know there is a iif but I can't seem to get it to work.
0
 
Jinesh KamdarCommented:
Try to frame your query based on the logic i used. If still not lucky, post your query.
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.

All Courses

From novice to tech pro — start learning today.