asked on

Flexible crosstab for aged debtor analysis

I need to set up an aged debtor analysis in a form. I have a query that is list of customers and a list of dates when they were due to pay for their purchases.

What I want to see is a list of customers in rows and a list of date buckets in columns (representing how far overdue the customers payments are) with the amount outstanding summed in each cell. Crosstab, yes, but I want the columns to be such that they are, e.g.

0-1 month overdue
1-2 months overdue
2-3 months overdue
3-6 months overdue
6+ months overdue

where I specify the current date either through a parameter or form.

Ideally, and this is the bit that gets me, I'd like it to be flexible enough for a base-level user to change the bucketing on the columns, perhaps by using a lookup table.

Any suggestions or hints?

ASKER CERTIFIED SOLUTION

kiddiec

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcallarse

Create a table (tblAging) with your aging definitions. Table will have fields Min, Max and Desc(ription). A sample record would have values, "0,30,0-30 Days Overdue".

Your crosstab query would be

TRANSFORM Sum(tblDebtor.Debt) AS SumOfDebt
SELECT tblDebtor.Name
FROM tblDebtor, tblAging
WHERE
(tblAging.Min is not null and tblAging.Max is not null and
DateDiff("d",tblDebtor.DebtDate,[myDate]) >= tblAging.Min and
DateDiff("d",tblDebtor.DebtDate,[myDate]) <= tblAging.Max)
) or
(tblAging.Min is null and tblAging.Max is not null and
DateDiff("d",tblDebtor.DebtDate,[myDate]) <= tblAging.Max
) or
(tblAging.Min is not null and tblAging.Max is null and
DateDiff("d",tblDebtor.DebtDate,[myDate]) >= tblAging.Min
)
GROUP BY tblDebtor.Name
PIVOT tblAging.Desc;

nico5038

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- Answered by: kiddiec
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)

Netminder

Per recommendation, force-accepted.

Netminder
EE Admin