We help IT Professionals succeed at work.

AR aging Report

2,307 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.

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
looks like a crosstab query is what you need.

can you post sample data

Author

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



CERTIFIED EXPERT
Top Expert 2016

Commented:
if duedate is more then 30 days where do you want it to show, say it is 35.

Author

Commented:
30 day is the header and underneath the header should have the sum total amount due for the cust id.
CERTIFIED EXPERT
Top Expert 2016

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..
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

Author

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.

Author

Commented:
hi jinesh kamdar,

i am using access 2000, i believe the case when is only allowed in SQL, sorry.
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?

Author

Commented:
I know there is a iif but I can't seem to get it to work.
Try to frame your query based on the logic i used. If still not lucky, post your query.
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.