We help IT Professionals succeed at work.

# AR aging Report

on
2,307 Views
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

## View Solution Only

CERTIFIED EXPERT
Top Expert 2016

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

can you post sample data

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.

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

Commented:
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

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.

Commented:
hi jinesh kamdar,

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

Commented:
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?

Commented:
I know there is a iif but I can't seem to get it to work.

Commented:
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 the solution to this question.