How? Multiple-fields in Crosstab query

[Shinken]

I need to make a crosstab query that displays data groupings from more than one column(field) in the

Given a table with string-type columns(fields) for gender, type and location, each with entries limited to those in a value-list, and also a date column(field). I need a query that will total data entries in the string fields, grouped by month, for a spreadsheet style display in a report. I can do crosstab queries of totals(count) for gender vs month, type vs month, & location vs month, but not all three. More specifically...

I can make the following crosstab queries, all from the same larger query:

Gender
Jan Feb Mar Apr .....
M 1 3 2 3
F 8 0 4 1

Type
Jan Feb Mar Apr .....
TypeA 1 3 2 3
TypeB 9 1 0 11
TypeC 3 1 3 1
etc

Location
Jan Feb Mar Apr .....
LocationA 3 2 0 1
LocationB 9 1 0 11
LocationC 5 1 3 5
etc

and so forth, but can't get them into a single crosstab query that I need for a report, which would look like this:

Jan Feb Mar Apr .....
M 1 3 2 3
F 8 0 4 1
TypeA 1 3 2 3
TypeB 9 1 0 11
TypeC 3 1 3 1
LocationA 3 2 0 1
LocationB 9 1 0 11
LocationC 5 1 3 5

How do I go about setting up a query like this? All data is string type except for the dates.

SECOND QUESTION:
Given a column(field) that has several string entries, such as "MPR" "FPR" "FPO" etc, how do I go about setting up a crosstab query that contains 2 rows, one for all "MPR" and another that totals all <>"MPR" rather than lists each <>"MPR" on a separate row?

Thanks,

Shin

 

[Shinken]

Correction to question

OOPS, the formatting of my posting needs correction. Here's an amended posting.

I need to make a crosstab query that displays rows from several fields(columns) in the table.

Given a table with string-type columns(fields) for gender, type and location, each with entries limited to those in a value-list, and also a date column(field). I need a query that will total data entries in the string fields, grouped by month, for a spreadsheet style display in a report. I can do crosstab queries of totals(count) for gender vs month, type vs month, & location vs month, but not all three. More specifically...

I can make the following crosstab queries, all from the same larger query:

Gender
.................Jan Feb Mar Apr
M............... 1 3 2 3
F................ 8 0 4 1

Type
..................Jan Feb Mar Apr
TypeA.......... 1.. 3... 2... 3
TypeB.......... 9.. 1... 0... 11
TypeC.......... 3.. 1... 3... 1
etc

Location
....................Jan Feb Mar Apr
LocationA...... 3... 2... 0.... 1
LocationB...... 9... 1... 0... 11
LocationC...... 5... 1... 3... 5
etc

and so forth, but can't get them into a single crosstab query that I need for a report, which would look like this:

.................Jan Feb Mar Apr
M................ 1... 3... 2... 3
F................. 8... 0... 4... 1
TypeA......... 1... 3... 2... 3
TypeB.......... 9.. 1.. 0.. 11
TypeC.......... 3.. 1.. 3.. 1
LocationA..... 3.. 2.. 0.. 1
LocationB..... 9.. 1.. 0.. 11
LocationC..... 5.. 1.. 3.. 5

How do I go about setting up a query like this? All data is string type except for the dates.

SECOND QUESTION:
Given a column(field) that has several string entries, such as "MPR" "FPR" "FPO" etc, how do I go about setting up a crosstab query that contains 2 rows, one for all "MPR" and another that totals all <>"MPR" rather than lists each <>"MPR" on a separate row?

Thanks,

Shin

 

[Pat Hartman]

1. Since there are no common columns between the three queries, use three separate subreports, one for each query.

2. Create a query to "calculate" the value you need and base the crosstab on that query rather than on the table.

Select IIf(YourField = "MPR", YourField, "<> MPR") as SomeField, fld1, fld2, etc.
From YourTable;