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?
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?