Reporting when Column header keeps changing

[lynxbci]

Hi
I have a crosstab query that reports KPI numbers for the last 5 weeks, using the week no as the column header.
Then I am generating a report from this query. It works 100%

However............

When I go to the next week, the report fails as the first week from the previous query no longer exists

So, this is happening...

The first report generated columns called 39,40,41,42,43 because the crosstab query uses week number as the header.

The second report (1 week later) cant find column 39 so it crashes!

Can't see how to change the column header to a generic as it is created by the crosstab.....

I hope that makes sense

 

[CJ_London]

You need to either:

1. create an alias for the week numbers so the columns names are the same (.e.g. Week A, Week B, etc), Then in your report use a bit of vba code to modify the column header label captions to Week 39, 40, etc.

or

2. fix the headers in your crosstab so all possible weeks are returned - 1-53 (to do this, in query design right click and select properties-column heading) and then in the report hide the columns not required.

 

[lynxbci]

Thank you I had opted for solution 1, and it now works with a little bit of IIF magic.

thank you

 

[Geotch]

I answered this earlier this week. Here is an example of how I handle the data and report.