Crosstab Column Headings

[Guest]

I have multiple crosstab queries that feed into different reports/subreports.
The queries sum data by certain categories (rows) for specific years
(columns). Right now, I've entered the years I want into the "Column
Headings" section of the Query Properties so I can run my reports. Problem
is, each year someone will have to go into each query and manually change
that field. Is there a way I can create a form or something else that will
allow the user to enter the year ranges they want each time instead of having
the years entered in the "Column Headings"? Here is the SQL for one of the
queries in case that will help.

TRANSFORM Sum([BPItI Add Cluster and Calendar Year]![N_MATL]*[Avg NA
Volumes]![% Avg NA]) AS [Avg Veh]
SELECT [BPItI Add Cluster and Calendar Year].Cluster
FROM [BPItI Add Cluster and Calendar Year] INNER JOIN [Avg NA Volumes] ON
([BPItI Add Cluster and Calendar Year].[Calendar Year] = [Avg NA
Volumes].Year) AND ([BPItI Add Cluster and Calendar Year].VLDESC = [Avg NA
Volumes].[Veh Desc])
WHERE ((([BPItI Add Cluster and Calendar Year].CLASS)="C" Or ([BPItI Add
Cluster and Calendar Year].CLASS)="R" Or ([BPItI Add Cluster and Calendar
Year].CLASS)="B" Or ([BPItI Add Cluster and Calendar Year].CLASS)="P" Or
([BPItI Add Cluster and Calendar Year].CLASS)="O") AND (([BPItI Add Cluster
and Calendar Year].[Calendar Year]) Between [Beginning Year] And [End Year]))
GROUP BY [BPItI Add Cluster and Calendar Year].Cluster
PIVOT [BPItI Add Cluster and Calendar Year].[Calendar Year] In
("2005","2006","2007","2008","2009");

Thanks!

 

[Duane Hookom]

You can have the user enter the ending year in a text box and then use
relative years as column headings
PIVOT "Yr" & Forms!frmYear!txtYear - [BPItI Add Cluster and Calendar
Year].[Calendar Year] In ("Yr0","Yr1","Yr2","Yr3","Yr4");

This will create a standard set of relative year columns. You will need to
set the query -> parameters to:
Forms!frmYear!txtYear Integer