473,386 Members | 1,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Row Headings in a Crosstab Query

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







Feb 19 '06 #1
2 2911
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" <ma****@EUnet.yu> wrote in message news:dt**********@news.eunet.yu...
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







Feb 21 '06 #2

"Nenad Markovic" <ma****@EUnet.yu> schrieb im Newsbeitrag news:dt**********@news.eunet.yu...
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







Mar 11 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
4
by: pw | last post by:
Hi, I have month names (coming from a field in a table) as the column heading in an Access 97 crosstab query. It is being sorted alphabetically. This will not do. The only way that I know to...
1
by: Steve | last post by:
I have looked through the newsgroup for an answer to this but haven't been able to find anything resembling my situation. What I want to do is relatively simple, I think. I have a crosstab...
3
by: Paula | last post by:
I need to do a summary using a crosstab query. The data has a Date field (Not named "Date"). I can do the Row Heading and Value but am having trouble with the Column Heading. The summary Columns...
1
by: maria obrien | last post by:
FYI Mr/Ms McDermott. sorry if i'm giving too much info and thank you for replying to my query. hope this will make my problem clearer. The term_cont table contains the contact records (with...
3
by: Lee | last post by:
I have a crosstab query that lists the number of patients(DECnum)by County. County is my Row Heading. Currently, the Column heading is Age in months, which is set at one month intervals. I would...
4
by: Jean | last post by:
Hello, can the following be done? I have a one-rowed query, with the following column headings and values: M-1 M-5 M-7 B-C-1 B-D-2 B-D-4 E-5 R-4 --- --- --- ----- ----- ----- ...
3
by: Col | last post by:
My table has rows that list type of pay with YTD pay for each as well as an employee ID number. I want to make the type of pay the column headings and have each row contain an employee id# and the...
4
by: odavison | last post by:
I'm currently running a crosstab query that displays the total amounts of Appointments that each Consultant. Currently it is Consultant ID as the Rows, and Appointment Date as the top headings,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.