NEED HELP with Report based on Crosstab Query

[Lillian]

PLEASE HELP!

I have a report based on a crosstab query. I need to have
4 columns of data show up in the report whether or not
they show up in the query. Some times, based on the
criteria, I have 1,2 or 3 of the 4 columns appear in the
query results and then get an error when the report looks
for the missing data.

Anyway, I have tried adapting the code from the example
microsoft has in the kb, however, I have summaries in
groups also, and I am not sure how to change the code.
Also, this code dynamically creates the headers too, and
doesn't make all columns show up, let alone in the same
order, each time the report is run.

I don't know what to do and don't have access to any
assistance. IS THERE ANOTHER WAY TO DO THIS?

Please help!

Lillian

 

[Duane Hookom]

If you know what the 4 possible values of column headings are, then enter
the values in the Column Headings property of the query.

If your column headings are date intervals then there is a much more
flexible method.

There are sample crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb allows
any number of dynamic columns.

 

[Lillian]

Thanks much. I have looked at your databases and you have
definitely used some creativity around this matter.

My 4 columns (category names) are the same all the time,
it is just a matter of whether there is data or not for
that column.

I need the header to show up in the same spot every time,
but hide the fields on the report if they don't exist.

Is there a way to just check to see if the column exists?
and if it doesn't just hide the field? I am just trying to
simplify, and also I am not very saavy with the code.

Thanks much!

Lillian.

 

[jsr]

Lillian,

Hit the wrong button and sent a replay as a new message,
(see crosstab reports0

The best way create a crosstab query with "guaranteed"
columns even is no data is present is modify the property
of the query. In design, click on properties, (right
click top) and in column headings, add whatever heading
you want. ex"heading1","heading2" etc.
This causes the query to generate columns even if no
data. Works every time.

Good luck