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

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

 

[Renate Steeg]

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