I have a working solution to this problem, but I'm sure that there is a better way to do it.
I have a crosstab query that produces data for each of the previous 12 months, i.e. a moving window and the report is run every month.
The report based on this query uses the Month name as the heading and then displays the data for each Department under that month. The problem is that the Month name changes every month and therefore the fieldname in the report is different each month.
To resolve this I have put in some VB code to calculate the month number in the query - the earliest month from this month becomes "month1" column heading through to "month12" for the latest month. I then recalculate the actual month name using a VB function in the report.
Anyone with experience in this got a better suggestion?
I have a crosstab query that produces data for each of the previous 12 months, i.e. a moving window and the report is run every month.
The report based on this query uses the Month name as the heading and then displays the data for each Department under that month. The problem is that the Month name changes every month and therefore the fieldname in the report is different each month.
To resolve this I have put in some VB code to calculate the month number in the query - the earliest month from this month becomes "month1" column heading through to "month12" for the latest month. I then recalculate the actual month name using a VB function in the report.
Anyone with experience in this got a better suggestion?
