Crosstab Query Row Heading

[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

 

[Crystal]

Hi Nesha,

Add your Classes table to the query.

Link on ClassID -- use Left Join
edit link line to show ALL records from Class
change grid to get ClassID from the Classes table

Is AreaID also in the Classes table?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Nenad Markovic]

Hi Crystal,



First, thank you very much for the reply. But, the thing is - it didn't
work. My Crosstab Query is based on the Select query, so I did what you said
in both Select and Crosstab Queries, but I got same results as before.



AreaID is in the separate - Areas table, but I don't have the problem with
AreaID since every area has a Tree (also in a separate table) with at least
one Class_1_ID (trees of Class_1_ID (rows) cross-tabulated with Class_2_ID
(columns) are counted in the Vlaue column), so every Area_ID is listed as a
row in the result.



Any other ideas?



Thank you again what so ever and regards from



Nesha

 

[Crystal]

Hello Nenad,

I am puzzled why that didn't work...

change your crosstab to a Select query to experiment with
the Joins until you get the records you want... then change
it back to a Crosstab. Without seeing your db and doing it
myself, it is hard to explain.

Can you post the SQL?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Crystal]

Hi Nesha,

Get rid of qryTrees and use the tables directly -- you need
to set the joins properly on all linked fields in order for
the records in tblClass_1 to show -- by using the query, you
have already eliminated them.

1. add the source tables to your crosstab
2. change the Table on the grid to source tables
3. delete the query
4, make all joins show all records in tblClass_1

"the bad feeling that this is not possible"

yes it is, have faith


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com