Column headers in a crosstab query.

[Robert Burns]

Hi all,

My question is, how can I set the Column Headers property of a crosstab query so that it will always force headers of the names of the preceding 3 months, as opposed to simply setting the property to the current preceding 3 months ie: "September", "October", "November"?

Thanks for any help anyone can give.

Rob.

 

[Rich]

Why not just restrict the output of the crosstab to the last three months and group by the Month?

 

[Robert Burns]

Cheers Rich,

However, the thing is that if there are no values for one month, then that column doesn't show in the crosstab query, and I would like all columns to be shown. Thats why I imagine that the column headers property is the answer.

 

[Rich]

Have you tried entering the Months for the whole year in the column headers and then restrict the output?

 

[Robert Burns]

If I put 12 monthnames in the column headers property, then restrict the crosstab query to the last 3 months, I still get 12 columns with headers, but only 3 months with data. However, if one of those 3 months contains no values, then I'm back to square one, because I can't see a way of extracting just those 3 columns!

 

[ColinEssex]

One way I did this was to create a pivot table in Excel and then you can select whatever months you want to show. I needed to do it in Excel anyway as I needed to "pretty up" the result.

Refreshing the data in Excel runs the crosstab queries in Access from Excel without opening Access.

Col

 

[Robert Burns]

Thanks Colin, and Rich too for your suggestions.

I have actually managed to tackle the problem by creating the crosstab query SQL from code, and creating the IN string that determines the column headers as below:

I set strDates = ""(" & """" & Format(DateAdd("m", -1, Date), "mmmm") & """" & "," & """" & Format(DateAdd("m", -2, Date), "mmmm") & """" & "," & """" & Format(DateAdd("m", -3, Date), "mmmm") & """" & ")"

and then set the last line of my SQL to:
...
...
strSQL = strSQL & " IN " & strDates & ";"

This seems to work fine.

Thanks again for your help.

Rob.