Setting Header Name on Crosstab Columns

[mbalent]

I've got the following query:

TRANSFORM Sum(CSRVolume_2.TotVolume) AS TotalVolume
SELECT CSRVolume_2.ResponsibleCSR, CSRVolume_2.GroupCode, CSRVolume_2.Customer, Max(CSRVolume_2.CompanyName) AS CustomerName
FROM CSRVolume_2
GROUP BY CSRVolume_2.ResponsibleCSR, CSRVolume_2.GroupCode, CSRVolume_2.Customer
PIVOT CSRVolume_2.BillingMonth;

The report I'm building from this will have a rolling previous three months data in three columns. I can't seem to get a static name for each (something like "Month-3, Month-2, Month-1"). I have tried using the column headings property but I always get a type mismatch error.

Any help would be appreciated.

Matt

"Nature forges everything on the anvil of time

 

[dhookom]

What is the data type of BillingMonth? Is it a text or date or numeric field? There is an FAQ in the Access Reports forum regarding creating a monthly crosstab report. It might provide some help depending on your data.

Duane
Hook'D on Access
MS Access MVP

 

[mbalent]

Thanks for the help. What I ended up doing is the following:

TRANSFORM Sum(CSRVolume_2.TotVolume) AS TotalVolume
SELECT CSRVolume_2.ResponsibleCSR, CSRVolume_2.GroupCode, CSRVolume_2.Customer, Max(CSRVolume_2.CompanyName) AS CustomerName
FROM CSRVolume_2
GROUP BY CSRVolume_2.ResponsibleCSR, CSRVolume_2.GroupCode, CSRVolume_2.Customer
PIVOT "Month" & DateDiff("m",Date(),[CSRVolume_2.BillingMonth]) In ("Month-3","Month-2","Month-1");

I made the BillingMonth column in the table a text field (not a date) since I wanted a generic header for the report. Since my query is always only going back three months (handled by other queries run prior to this one to populate the data in CSRVolume_2) I was able to 'hard code' the column headers of 'Month-3, Month-2, and Month-1'. In the report itself I then used expressions to set the appropriate 'month-year' column heading so the report always shows the proper period the data was reporting regardless of when it is run (in my case "Dec-08", "Jan-09" and "Feb-09" for a report run in March 09).

Matt

"Nature forges everything on the anvil of time

 

[dhookom]

If this works and BillingMonth is text then the query is converting the text to a date data type in order to calculate:

DateDiff("m",Date(),[CSRVolume_2.BillingMonth])

I would be concerned about this conversion as you reference months in 2 different years.

Duane
Hook'D on Access
MS Access MVP