By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

Dynamic crosstab?

P: 13
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.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM CCUR(NZ(Count([qryData].[AMT]),0)) AS SumOfAMT 
  2.  
  3. SELECT DISTINCT ROW qry.BANK, qry.ACCOUNT, qry.CENTER, qry.FUNCTION, qry.DEPT,  
  4.  
  5. FROM qryData
  6.  
  7. WHERE ((qryData.[C/D])='C') 
  8.  
  9. GROUP BY [qryData].BANK, [qryData].ACCOUNT, [qryData].CENTER, [qryData].FUNCTION, [qryData].DEPT 
  10.  
  11. 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
May 1 '12 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.