Null Column Headings in Crosstab affects Report?

[Guest]

Hello,

I have a report that is based on a crosstab query. The question I have is
this. What if a a category choice in my column heading doesn't appear in
next's month's data......and a new one appears? That will change the column
headings in my crosstab query.....and it will mess up my report as a result.
How do I get around this?

Thank you!
MN

 

[Duane Hookom]

You can find some samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. There is a Crosstab.mdb
that might meet your needs.

 

[Guest]

Thank you, this is helpful for visual purposes, but are there instructions
somewhere that I could look at to take me through this step by step?

Thank you!
MN

 

[Duane Hookom]

There is a table in the mdb "ztblExplanation" that might help you
understand:
==================================
Are you familiar with Crosstab Queries? Crosstab queries will dynamically
create columns/fields based on each unique value of a particular field in
your query. I my demo, I used customer names as columns.

The problem is:
1) the number of columns created by the crosstab will vary based on the
number of customers during a particular period of time, the type of products
included in your report, and the sales persons reported. There might be 2 or
there might be 20. We don't know until the crosstab is run.

2) reports expect a certain number of fields in their record source. This
generally isn't a problem since we base most reports on fields/columns from
a query in which the fields don't change. The records will change but the
same base columns will generally be available. Since Crosstab queries will
vary in the number and names of fields, reporting them takes some work
arounds.

My method starts with a desire to predefine the column/field names in the
report. This could be numbers, colors, or whatever. I chose letters of the
alphabet since they easily afford up to 26 columns, alphabetic order is
something we all understand, they have an ASCII value that converts to
numbers using the ASC() function, and "A" is easier to type then "Yellow".

I can now create an "alias" table of two fields, the customer name and the
associated letter of the alphabet. If I include this table in my crosstab
and link the customer names, I can use the associated letter as the column
heading.

This works fine except that different sales people will sell to different
customers. So we add a salesperson field to the "alias" table. We can create
a totals query "qappEmpCust" that will append every unique combination of
salesperson and customer to the Alias table. I then use code to loop through
the table and assign the letters "A-F" or whatever.

This works fine until one salesperson sells to more than 6 customers. So, I
add another field that describes the level (couldn't find a better term).
The code loop then assigns level 0 letters A-F and then increments to level
1 and starts over at A etc.

The final piece was to provide column headings. This was done using a label
type subreport where the link master child fields reference the Salesperson
and Level displaying the customer names rather than A-F.

All of this together allows me to successfully report the result of a
crosstab query regardless of the number of potential columns and their
names. The report also runs much faster than a solution provided in the
Microsoft sample Solutions.MDB.

Are you even more confused? To be honest with you, I have to re-think what I
did each time a create a new report. One of my employees has used this
method for about 6-8 reports. She figures this method has saved her a couple
days of development time and kept a client much more satisfied.
==================================

 

[Guest]

Thank you!

There is a table in the mdb "ztblExplanation" that might help you
understand:
==================================
Are you familiar with Crosstab Queries? Crosstab queries will dynamically
create columns/fields based on each unique value of a particular field in
your query. I my demo, I used customer names as columns.

The problem is:
1) the number of columns created by the crosstab will vary based on the
number of customers during a particular period of time, the type of products
included in your report, and the sales persons reported. There might be 2 or
there might be 20. We don't know until the crosstab is run.

2) reports expect a certain number of fields in their record source. This
generally isn't a problem since we base most reports on fields/columns from
a query in which the fields don't change. The records will change but the
same base columns will generally be available. Since Crosstab queries will
vary in the number and names of fields, reporting them takes some work
arounds.

My method starts with a desire to predefine the column/field names in the
report. This could be numbers, colors, or whatever. I chose letters of the
alphabet since they easily afford up to 26 columns, alphabetic order is
something we all understand, they have an ASCII value that converts to
numbers using the ASC() function, and "A" is easier to type then "Yellow".

I can now create an "alias" table of two fields, the customer name and the
associated letter of the alphabet. If I include this table in my crosstab
and link the customer names, I can use the associated letter as the column
heading.

This works fine except that different sales people will sell to different
customers. So we add a salesperson field to the "alias" table. We can create
a totals query "qappEmpCust" that will append every unique combination of
salesperson and customer to the Alias table. I then use code to loop through
the table and assign the letters "A-F" or whatever.

This works fine until one salesperson sells to more than 6 customers. So, I
add another field that describes the level (couldn't find a better term).
The code loop then assigns level 0 letters A-F and then increments to level
1 and starts over at A etc.

The final piece was to provide column headings. This was done using a label
type subreport where the link master child fields reference the Salesperson
and Level displaying the customer names rather than A-F.

All of this together allows me to successfully report the result of a
crosstab query regardless of the number of potential columns and their
names. The report also runs much faster than a solution provided in the
Microsoft sample Solutions.MDB.

Are you even more confused? To be honest with you, I have to re-think what I
did each time a create a new report. One of my employees has used this
method for about 6-8 reports. She figures this method has saved her a couple
days of development time and kept a client much more satisfied.
==================================

 

[Guest]

Hello again,

I used that code to assign Single Letters as an alias to each category.
Now....for my report....how to display however many columns currently need to
be shown. I don't want to have an "lbocolumns" and have the user select how
many columns the report WILL be. I want that to be automatic based on that
table i created containers the categories with their corresponding aliases.
How do I go about doing this so I don't get an error when a category is null.
i.e. if category "G" was not used this time but is on the report.

Thank you much!
MN

 

[Duane Hookom]

You can call the function with a specific number of columns. This would be
the maximum number of columns that would fit in your report. This would
basically be "hard-coding" the value that had been dynamically set by the
list box.