Variable # of Column Headings in Crosstab Report

[Guest]

I wish to creat a report from a crosstab query based on a table: the query
Row Headers are Employees, the column headers are codes relating to actions.
I used a wizard to create a report upon this crosstab query and it worked
fine until a new code was introduced. Now, the new code does not appear as a
column header even tough the TOTAL # of actions (in a separate calculated SUM
field in the report footer) is counted (The underlying query works fine and
counts everything): BUT I need to also be able to view the # of actions
under each particular column heading in the REPORT. These column headings
are variable - since the type of actions they represent will change over
time; therefore the format of the report in regards to these column headings
will also be variable. How can I resolve this?

I also wish to specify a date range for this report based on the date(s) of
when these coded actions in the column headers occured. Is THAT possible?

THANK YOU wholeheartedly!!

 

[Allen Browne]

It seems that you cannot use the basic approach of specifying all possible
actions in the Column Headings property of the report, as suggested here:
http://allenbrowne.com/ser-67.html#ColHead

So, it sounds like you will need to write some code in the Open event of the
report, that configures the report run to give the desired result. Note that
at this stage, you cannot use this solution in Access 2007: it will crash
Access. We are hoping MS fixes this when they release SP1 for Office 2007.

The basic idea is to:

1. Design the report with the maximum number of columns you could need at
any one time. Up to 255, though realistically this is limited by available
space.

2. Add unbound text boxes named something like txt0, txt1, txt2, ..., and
set the Format property to indicate the data type (Currency, General Number,
or blank for Text type.)

3. Add labels for the column headings: lbl0, lbl1, lbl2, etc. leaving their
Caption blank.

4. In Report_Open, write code to:
a) OpenRecordset on a query to SELECT DISTINCT the actions you need in the
date range, sorted the way you want the columns. Cancel if there are no
records.

b) Loop through the records of the recordset, assigning the ControlSource of
txt0, txt1, etc and the Caption of lbl0, lbl1, etc. At the same time, build
up a string that you will use as the PIVOT clause for your crosstab. Warn
the user if there were more actions than you have text boxes for.

c) Hide the unused text boxes and labels, and set the Left and Width of the
others to take advantage of available space.

d) Build the crosstab query statement with the WHERE clause for the dates
and the PIVOT clause based on the actions you need. Assign it to the
RecordSource of the report.

 

[Guest]

I much prefer the Crosstab solution found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This one
uses much less code and uses bound text boxes from design time. You can build
the report for a set number of columns. If your data results in more than
your set number of columns, the additional columns "wrap" under the first.
This solution also runs much quicker.