Accommodate Variable Number of Columns

[terri]

I am using 2000RT.

I have a crosstab query that I use with Transform to create a pivot table
that shows the value of an instrument master table as its row value, with a
count of how many times the instrument appears in a related table. The
results are:

Instrument Proj1 Proj2 Proj3 …
Ins1 # # #
Ins2 # # #
Ins3 # # #
…
The query is working fine, and I can view it on screen or send it to Excel
without a problem. I would like to use it as a recordsource in a report.
User supplied parameters mean there is no pre-set number of rows/columns
returned. Switching the pivot (Project vs Instrument) can not guarantee that
the number of columns would fit across a single page. In fact, the
parameters are to ensure filtering limits the number of columns to the query
maximum.

What I would like to see printed is:

Page 1
Instrument Proj1 Proj2 Proj3 … Proj10
Ins1 # # #
Ins2 # # #
Ins3 # # #
…
Page 2
Instrument Proj1 Proj2 Proj3 … Proj10
Ins4 # # #
Ins5 # # #
Ins6 # # #
…
Page 3
Instrument Proj11 Proj12 Proj13 … Proj20
Ins1 # # #
Ins2 # # #
Ins3 # # #
…
Page 4
Instrument Proj11 Proj12 Proj13 … Proj20
Ins 4 # # #
Ins 5 # # #
Ins 6 # # #
…

The only thing I can think of is to have a static set 10 columns on the
report and transfer the query data into a table with 10 Proj columns. So if
10-20 Projs were returned, I’d have to create 2 records for every Ins, 3 for
30 Projs, 4 for 40 etc.

Is there a better/easier way?

Thanks for any insight.

 

[Duane Hookom]

There is a solution "Crosstab" download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[terri]

Thanks Duane!

I downloaded your example file and it looks promising. I'm going to work
with it later today.