How to show empty Crosstab query columns? 5

[Kraeg]

Let's say I have three tables as below (this gives the basic idea of my problem; the actual tables and data are bigger and more complex):

Customers
---------
CustomerID (PK)
CustomerName
StateID (FK to States.StateID)
AgeID (FK to Ages.AgeID)

States
------
StateID (PK)
StateName

Ages
----
AgeID (PK)
AgeRange

I want to run a Crosstab query that will have StateName as the Row Heading, AgeRange as the Column Heading, and a Count of the number of Customers of each AgeRange in each StatName.

However, if there are no Customers in an Age Range that is listed in the Ages Table, that Age Range won't appear as a Column; I'd like it to (and have it blank/full of 0s).

Is there a way to make a Crosstab query do this?

 

[Kraeg]

Well, it appears I have it working (by using the ColumnHeadings Property of the Query)..... but now I need to work on the Row Headings (for example, if there are no Customers in a State, still show that StateName as a Row Heading).

 

[e7akerson]

Kraeg,

How did you do it? You mentioned using the ColumnHeadings property of the Query. How does this help to eliminate the problem? I am having the same problem even in a smaller crosstab query and I only have one table so I can learn how to take care of this.

 

[Kraeg]

Let's say I have these Age Ranges:
01-20
21-50
51-99

I want these as Column Headings, but if there isn't anyone in the 51-99 Age Range, it won't appear as an empty Column.

But if I put the following into the ColumnHeadings property of the Crosstab Query, all three ranges appear:
"01-20";"21-50";"51-99"

 

[e777akerson]

OK, next question. How do you get it to list zeros in the columns where there are no values? Does your crosstab query do this now?

 

[Kraeg]

To have it show zero values, create a query based on the Crosstab query. The first Column will be the Rows from the Crosstab, and the remaining Columns will be the values.

For each value Column, create an expression that shows 0 if the value is Null, otherwise show the value.

For example:
51 to 99: IIf(IsNull([51-99])=True,0,[51-99])
Will show 0 when there is no-one in that age range (in either individual states or in all states).

Now to work out the next step..... show blank Rows.

 

[e777akerson]

I got desperate and did pay an Access consultant for the solution to my particular problem. The answer is

CountCategory: IIf(Count([Category]) Is Null,0,Count([category]))

The Total line of the crosstab for Count Category is Expression and the Crosstab line is Value. As you can see I haven't done enough work with crosstabs.