Dynamic crosstab?

[Andrew Hulting]

I am creating a process to dynamically age receivables. I do have standard aging of 15 day buckets up to 90. The issue is that there are a few processes that need custom aging, IE: 7 day buckets. I have crosstabs to do the aging, with the age in days as column headers, I default anything greater than 90 days to 91. I also separate it to debits and credits by count than sum. Each crosstab had 96 columns, 1-91 for age and 5 for other identifiers. So condensing it to one is not an option. I am taking the 4 crosstab queries and am joining them to a master list; I should like to be able to sum up the items in the specific range, with the default of 15 day buckets and the option to custom buckets. I am struggling on how to accomplish this any thoughts, of making something of such. This is access2003.

TRANSFORM CCUR(NZ(Count([qryData].[AMT]),0)) AS SumOfAMT 
 
SELECT DISTINCT ROW qry.BANK, qry.ACCOUNT, qry.CENTER, qry.FUNCTION, qry.DEPT,  
 
FROM qryData
 
WHERE ((qryData.[C/D])='C') 
 
GROUP BY [qryData].BANK, [qryData].ACCOUNT, [qryData].CENTER, [qryData].FUNCTION, [qryData].DEPT 
 
PIVOT IIf(([RecDate]-[ ItemDate]+1)<91,[ RecDate]-[ItemDate]+1,91) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91);

Thanks,
Andrew