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?