Crosstab Row Heading w/ zero Value

[Guest]

When I use a crosstab and there is no data to total in the column the query
will not list the rowheading in the output.

Example: If I have three sales agents (one is Joe) and I run a crosstab with
the agent names in the row, the locations in the Column, the sales as the sum
value, and the location has a criteria of a specific destination (Vegas), if
Joe has no sales to Vagas his name will not appear on the row heading.

I would still like to see Joe's name in the rows. Is there a way to make
that happen?

 

[Allen Browne]

List all the agents names in the Column Headings property of the query.

In query design view, open the Properties box (View menu.)
Make sure you are looking at the properties of the query, not those of a
field.
The entry will be something like this:
"Joe"; "Mary", "Fred"

 

[Guest]

This was a really simplified example. But to stick with it. Lets say I am
looking at sales for every sales agent around the world (and they are all in
my database). Am I understanding correctly that I would have physically type
every name that exists? Is there a shortcut, like a source record
(table.field)?

 

[Allen Browne]

If you understand how to loop through a recordset and build up a string by
concatenating the value from each record, then yes, you can assign the SQL
property of the QueryDef at runtime.

You won't get all the salesmen in the world into a crosstab though. It is
limited to 255 columns.

 

[Guest]

OK. I am nowhere near that smart.
However, a related question. If the agents names are supposed to be in the
row headings, why is it that I need to enter the names as column headings.
Seems to me that the data gets turned on it's axis that way.

 

[Duane Hookom]

I think there was some misunderstanding here regarding columns and rows.
When you originally mentioned "Joe" not showing for "Vagas" did you actually
mean Joe does not show a value in any column so his name doesn't appear or
are you only concerned that under the column Vagas at the row for Joe, there
is no value?

If sales agents are the "Row Heading", then you should be able to accomodate
all agents easily in a crosstab. You may need to use a LEFT or RIGHT JOIN to
get agents to appear even if they don't have any sales (or whatever).