Flexible crosstab for aged debtor analysis

mjstreet
mjstreet used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Add another field to your query and insert the following
IIF(dateDiff("m",[InvoiceDate],Date()) = 1,"0 - 1 Month Overdue",IIF((dateDiff("m",[InvoiceDate],Date()) = 2,"1 - 2 Months Overdue","Current"))  and so on.

You can then create a crosstab query to display the data.  

When you create your crosstab you can set the column headings by typing in exactly the same as the result from the query, so that you can be sure the data is displayed in the correct order

For instance "Current";"0 - 1 Month Overdue";"1 - 2 Months Overdue".... and so on
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;

Commented:

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)
Per recommendation, force-accepted.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial