Row Headings in a Crosstab Query

[Nenad Markovic]

Hi everybody,

 

When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can I make a Crosstab Query that shows all rows, regardless they have values in column headings.

For instance, the result that I get now is like this

 

           

AreaID             Class_1_ID      Class_2_Val_1 Class_2_val_2              Total_Class_1_ID

1                      2                      10                                20                                30

1                      3                      5                                  15                                20

2                      1                      6                                  30                                36

2                      2                      10                                                                    10

2                      3                      20                                20                                40

3                      1                      10                                10                                20

3                      2                      15                                15                                30

3                      3                      10                                10                                20

4                      1                      15                                20                                35

4                      3                      10                                15                                25

 

AreaID, Class_1_ID and Total_Class_1_ID are Row Headings;

Class_2 is the Column Heading

The Value field is defined as expression: Count(….

 

So, for the Area 1, in Class_1_ID=1 there are no values for neither column in Crosstab result, so it is not shown. For the Area 4 there are no values for Class_1_ID=2 for neither column in Crosstab result, so the whole row is not shown. Of course, the Class_1_ID values are prederfined and limited (there are classes 1, 2 and 3)

 

Is there a way to show these rows? Or can this be solved in a Report based on this Crosstab Query? Something like Column Headings property that can be defined in a Query properties dialog box, but for rows?

 

Thanking you in advance,

 

Nesha

 

 

 

[Tom Ellison]

Dear Nenad:

 

I take it your second column "Class_1_ID" is key to understanding what you mean.  It does not always have all 3 values, 1, 2, and 3.

 

The fix is not withing the crosstab, but in having an underlying query that provides all these combinations.  I suggest it needs to be a cross-product to do this.

 

If what this means is not clear to you, I could give you the code for it, but that would require I first see much more detail of what you have.  Expecially, I need to know the tables that list every AreaID and, independently, every Class_1_ID.

 

Tom Ellison

 

"Nenad Markovic" <mar...@EUnet.yu> wrote in message news:dt9fq2$rft$1...@news.eunet.yu...

[Renate Steeg]

 

"Nenad Markovic" <mar...@EUnet.yu> schrieb im Newsbeitrag news:dt9fq2$rft$1...@news.eunet.yu...