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

Crosstab Report with Dynamic Columns

100+
P: 294
Suppose I am trying to show data for years something occurred.

Data, in the table, might look like:

Expand|Select|Wrap|Line Numbers
  1. Event   Year
  2. 1       2007
  3. 1       2008
  4. 1       2009
  5. 2       2009
  6. 2       2010
When I go to create the report using a query that pulls this data (using wizard), it asks me if I want to add all of the fields, which I do.

However, If I were to close the report, and additional records were added with Years > 2010, it would cut them off because they are not on the report. 2010 would be the latest year on the report because that's all that was added when I created it using the wizard.

Is there a way to handle this exception? Say...create the report using VBA, that way I don't have to manually define how much columns there may/may not be?
Jun 2 '14 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,240
You can pre-assign column names in the properties of the query. You could assign a column for 2015 even though no data will appear there yet. Look for 'column headings' in the properties of the upper half of the query editor. Then you could add code to the format event(s) of the report to hide empty columns.

You could concatenate column names/titles and data into one string that would simply grow with each new year's column. Or you could do something more elaborate by parsing the column names of the completed query. It's hard to know where to go with it without knowing more about your situation and details that might help determine the "right" design choices.

Jim
Jun 2 '14 #2

100+
P: 294
Jim, thanks for your answer.

I had pondered some of the things you mentioned from doing research before asking.

Is there a way to create and run the report at run-time? That way it has only what it needs, without additional formatting/hiding columns? Similar to what the Report wizard does?
Jun 2 '14 #3

Expert 100+
P: 1,240
Yes, but only with a considerable amount of coding. That's sort of telling it mildly. Probably not your first, best option. Maybe you should consider making each year appear as a detail row instead of a column. Is that possible?
Jun 2 '14 #4

100+
P: 294
Unfortunately not, because of user specifications. The Pivot returns data exactly as I need it to. It's just accommodating the differences in dates to be dynamic that is going to be the issue.

I would almost rather see if the user would accept an Excel output over something like this, unfortunately.
Jun 2 '14 #5

Expert Mod 2.5K+
P: 2,545
If you want to use Excel to format the output of an Access query instead of using an Access report, then depending on the pattern of years you expect to report upon (e.g. always reporting on a six year period which includes the current year) it is possible to design a crosstab query, say, which always has the same number of columns, regardless of the data pivoted, by using an additional static table to store all of the years you want to report. Access reports are not flexible, because as Jim said the columns included can only really be changed by using VBA coding.

Using a static lookup table to store all potential years you can then devise a query which uses the current date, say, to determine what base year to report. For six consecutive years, this could be something like:

Expand|Select|Wrap|Line Numbers
  1. Select TheYear from tblYears 
  2. Where TheYear Between Year(Date())- 5 And Year(Date());
If you were to name this, say, qryYears, you can add it to your crosstab query and left- or right-join it on the Year field of your crosstab's source query, using its Year field as the pivot.

The test data below shows an example of what can be achieved that way. Because the years query uses the current date to determine the years range it is dynamic in that it will roll over as the year changes. Similar approaches can be taken for financial years, though the lookup table concerned is likely to be much more complex. For the reports I generate I use a static calendar lookup table with all days from 1 April 2009 to a very speculative end of March 2025.

Test data and SQL exemplar shown below.

Expand|Select|Wrap|Line Numbers
  1. Test table tblYears
  2. TheYear
  3. 2009
  4. 2010
  5. 2011
  6. 2012
  7. 2013
  8. 2014
  9. 2015
  10. 2016
  11. 2017
  12.  
  13. Output of qryYears
  14. TheYear
  15. 2009
  16. 2010
  17. 2011
  18. 2012
  19. 2013
  20. 2014
  21.  
  22. Test Data in tblTest
  23. SomeData       Amount  TheYear
  24. First Row       100     2010
  25. Second Row      200     2010
  26. Third Row       300     2014
  27. Fourth Row      400     2013
  28.  
  29. Test Crosstab
  30. Heading   2010   2013   2014
  31. CT Test    300    400    300
  32.  
  33. SQL
  34. TRANSFORM Sum(tblTest.Amount) AS Total
  35. SELECT 'CT Test' AS Heading
  36. FROM tblTest
  37. GROUP BY 'CT Test'
  38. PIVOT tblTest.TheYear;
  39.  
  40. Modified Crosstab pivoted on qryYears.TheYear
  41. Heading   2009   2010   2011   2012   2013   2014
  42. CT Test           300                  400    300
  43.  
  44. SQL
  45. TRANSFORM Sum(tblTest.Amount) AS Total
  46. SELECT 'CT Test' AS Heading
  47. FROM qryYears LEFT JOIN tblTest ON qryYears.TheYear = tblTest.TheYear
  48. GROUP BY 'CT Test'
  49. PIVOT qryYears.TheYear;
Stewart
Jun 3 '14 #6

Expert Mod 2.5K+
P: 2,545
Here's a related but different approach that does away with the crosstab altogether, by using a defined set of static columns that sum the amounts relative to a base year. It does not need a lookup table with all the years present, as it uses a simple subquery to lookup the maximum year from the sample data.

This type of query can be used in an Access report, and with a bit of tailoring of the report headings (changing the heading labels to be computed textboxes instead of static labels) the report can show the appropriate years without you having to change the column names in code. The first heading field based on what is shown below would have a row source of [Base Year] - 5, the second [Base Year] - 4 and so on. You'd need to add the base year field to your report somewhere to refer to it in an expression. This could be in place of the Y0 label. Otherwise, if you add it somewhere else you'd set its Visible property to No so that it is not visible directly in the output.

Expand|Select|Wrap|Line Numbers
  1. SELECT 'Non CT Test' AS Heading, 
  2. (SELECT Max(TheYear) AS [Y] FROM tblTest) AS [Base Year], 
  3.  Sum(IIf([TheYear]=[Base Year]-5,[Amount],0)) AS Y5, 
  4.  Sum(IIf([TheYear]=[Base Year]-4,[Amount],0)) AS Y4, 
  5.  Sum(IIf([TheYear]=[Base Year]-3,[Amount],0)) AS Y3, 
  6.  Sum(IIf([TheYear]=[Base Year]-2,[Amount],0)) AS Y2, 
  7.  Sum(IIf([TheYear]=[Base Year]-1,[Amount],0)) AS Y1, 
  8.  Sum(IIf([TheYear]=[Base Year],[Amount],0)) AS Y0
  9. FROM tblTest
  10. GROUP BY 'Non CT Test';
  11.  
  12. Sample Output
  13. Heading      Base Year  Y5   Y4   Y3   Y2   Y1   Y0
  14. Non CT Test     2014     0  300    0    0  400  300
  15.  
Stewart
Jun 3 '14 #7

100+
P: 294
Thanks for the reply, Stewart. I will take a look at your first example. I think that is closer to what I may need.
Jun 5 '14 #8

Post your reply

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