Crosstab queries

T

Tom

I have a table that contains 4 fields (State, Q1, Q2, Q3; while Q=Question).

As of now, the table contains 8 records. I need to come up with some matrix
(crosstab query) that shows a count of "answers" in the most elegant way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the "combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the output would
look like if I run all 3 crosstab... they columns don't line up properly.

To recap, the following data somehow should be grouped in some form of a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of "To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet, this all
will make much more sense.


Thanks,
Tom
 
D

Duane Hookom

First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;
 
T

Tom

Duane:

Thanks, this works great!!!

Just one more thing... let's say I only have 2 records in the table. If
the 2 survey participants would select only the value "To a great extent",
then my queries would not pick up the other four "answers" (Very great
extent, some extent, etc.).

Is there a way to always show all 5 "answers"... and if no one selected
them, populate the crosstab with a default value of 0.

That also would mean that I could link this query to a report with all 5
"answers" and line up the answer in a more sequencial order... from "very
great extent" to "very little extent".

Thanks in advance,
Tom


Duane Hookom said:
First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;

--
Duane Hookom
MS Access MVP


Tom said:
I have a table that contains 4 fields (State, Q1, Q2, Q3; while
Q=Question).

As of now, the table contains 8 records. I need to come up with some
matrix
(crosstab query) that shows a count of "answers" in the most elegant way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the "combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the output
would
look like if I run all 3 crosstab... they columns don't line up properly.

To recap, the following data somehow should be grouped in some form of a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of "To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet, this
all
will make much more sense.


Thanks,
Tom
Click to expand...
Click to expand...
 
D

Duane Hookom

You can use the Column Headings property of the crosstab query to specify
all column headings.

--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks, this works great!!!

Just one more thing... let's say I only have 2 records in the table. If
the 2 survey participants would select only the value "To a great extent",
then my queries would not pick up the other four "answers" (Very great
extent, some extent, etc.).

Is there a way to always show all 5 "answers"... and if no one selected
them, populate the crosstab with a default value of 0.

That also would mean that I could link this query to a report with all 5
"answers" and line up the answer in a more sequencial order... from "very
great extent" to "very little extent".

Thanks in advance,
Tom


Duane Hookom said:
First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;

--
Duane Hookom
MS Access MVP


Tom said:
I have a table that contains 4 fields (State, Q1, Q2, Q3; while
Q=Question).

As of now, the table contains 8 records. I need to come up with some
matrix
(crosstab query) that shows a count of "answers" in the most elegant way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the "combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the output
would
look like if I run all 3 crosstab... they columns don't line up properly.

To recap, the following data somehow should be grouped in some form of
a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of
"To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet, this
all
will make much more sense.


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Duane:

hmh, not sure if I'm following you on this.

In the Union query, I end up with the "Response" colum that contains all
records and the appropriate response.

Let's say, if the Response column has records A, B, C, I end up with the
fields "A", "B", and "C" in the 2nd query, right?

How would I get the fields e.g. "D" and "E" in the 2nd query if such values
do not exist at all in the Union query (records D and E)?

Tom

--
Thanks,
Tom


Duane Hookom said:
You can use the Column Headings property of the crosstab query to specify
all column headings.

--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks, this works great!!!

Just one more thing... let's say I only have 2 records in the table. If
the 2 survey participants would select only the value "To a great
extent",
then my queries would not pick up the other four "answers" (Very great
extent, some extent, etc.).

Is there a way to always show all 5 "answers"... and if no one selected
them, populate the crosstab with a default value of 0.

That also would mean that I could link this query to a report with all 5
"answers" and line up the answer in a more sequencial order... from "very
great extent" to "very little extent".

Thanks in advance,
Tom


Duane Hookom said:
First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;

--
Duane Hookom
MS Access MVP


I have a table that contains 4 fields (State, Q1, Q2, Q3; while
Q=Question).

As of now, the table contains 8 records. I need to come up with some
matrix
(crosstab query) that shows a count of "answers" in the most elegant way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the "combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the output
would
look like if I run all 3 crosstab... they columns don't line up properly.

To recap, the following data somehow should be grouped in some form of
a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of
"To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet,
this
all
will make much more sense.


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You would open the crosstab query and view its properties. Find the Column
Headings property and enter:
Column Headings: "A","B","C","D","E"

--
Duane Hookom
MS Access MVP


Tom said:
Duane:

hmh, not sure if I'm following you on this.

In the Union query, I end up with the "Response" colum that contains all
records and the appropriate response.

Let's say, if the Response column has records A, B, C, I end up with the
fields "A", "B", and "C" in the 2nd query, right?

How would I get the fields e.g. "D" and "E" in the 2nd query if such
values do not exist at all in the Union query (records D and E)?

Tom

--
Thanks,
Tom


Duane Hookom said:
You can use the Column Headings property of the crosstab query to specify
all column headings.

--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks, this works great!!!

Just one more thing... let's say I only have 2 records in the table.
If
the 2 survey participants would select only the value "To a great
extent",
then my queries would not pick up the other four "answers" (Very great
extent, some extent, etc.).

Is there a way to always show all 5 "answers"... and if no one selected
them, populate the crosstab with a default value of 0.

That also would mean that I could link this query to a report with all 5
"answers" and line up the answer in a more sequencial order... from
"very
great extent" to "very little extent".

Thanks in advance,
Tom


First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;

--
Duane Hookom
MS Access MVP


I have a table that contains 4 fields (State, Q1, Q2, Q3; while
Q=Question).

As of now, the table contains 8 records. I need to come up with some
matrix
(crosstab query) that shows a count of "answers" in the most elegant
way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the "combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the
output
would
look like if I run all 3 crosstab... they columns don't line up
properly.

To recap, the following data somehow should be grouped in some form
of a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of
"To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet,
this
all
will make much more sense.


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Duane:

that's perfect!!! Thanks so much for providing this solution.

--
Thanks,
Tom


Duane Hookom said:
You would open the crosstab query and view its properties. Find the Column
Headings property and enter:
Column Headings: "A","B","C","D","E"

--
Duane Hookom
MS Access MVP


Tom said:
Duane:

hmh, not sure if I'm following you on this.

In the Union query, I end up with the "Response" colum that contains all
records and the appropriate response.

Let's say, if the Response column has records A, B, C, I end up with the
fields "A", "B", and "C" in the 2nd query, right?

How would I get the fields e.g. "D" and "E" in the 2nd query if such
values do not exist at all in the Union query (records D and E)?

Tom

--
Thanks,
Tom


Duane Hookom said:
You can use the Column Headings property of the crosstab query to
specify all column headings.

--
Duane Hookom
MS Access MVP
--

Duane:

Thanks, this works great!!!

Just one more thing... let's say I only have 2 records in the table. If
the 2 survey participants would select only the value "To a great
extent",
then my queries would not pick up the other four "answers" (Very great
extent, some extent, etc.).

Is there a way to always show all 5 "answers"... and if no one selected
them, populate the crosstab with a default value of 0.

That also would mean that I could link this query to a report with all
5
"answers" and line up the answer in a more sequencial order... from
"very
great extent" to "very little extent".

Thanks in advance,
Tom


First, normalize your table with a union query (quniSurvey)
SELECT tblSurvey.recID, tblSurvey.State,
tblSurvey.Q1 AS Response, "Q1" AS Q
FROM tblSurvey
UNION ALL
SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q2, "Q2"
FROM tblSurvey
UNION ALL SELECT tblSurvey.recID, tblSurvey.State, tblSurvey.Q3, "Q3"
FROM tblSurvey;

Then create a single crosstab query based on your union query:

TRANSFORM Nz(Count([recID]),0) AS Expr1
SELECT quniSurvey.State, quniSurvey.Q
FROM quniSurvey
GROUP BY quniSurvey.State, quniSurvey.Q
PIVOT quniSurvey.Response;

--
Duane Hookom
MS Access MVP


I have a table that contains 4 fields (State, Q1, Q2, Q3; while
Q=Question).

As of now, the table contains 8 records. I need to come up with
some
matrix
(crosstab query) that shows a count of "answers" in the most elegant
way.

To make more sense of the data, I posted a small sample db
(CrosstabData.zip) at:

http://tombock2004.i8.com/Test

- it contains the table
- plus 3 crosstab queries

The SQL of the 3 crosstabs are listed below:

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q1
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q1;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q2
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q2;

======================================================

TRANSFORM Count(tblSurvey.recID) AS CountOfrecID
SELECT tblSurvey.State, Count(tblSurvey.recID) AS Q3
FROM tblSurvey
GROUP BY tblSurvey.State
PIVOT tblSurvey.Q3;

======================================================

Unfortunately, based on the answers found in the table, the
"combined
output" doesn't look "right". In some instances, I may only have 2
columns. Other times, I end up with 3 columns.

Please view the Spreadsheet "CrosstabData"... it shows what the
output
would
look like if I run all 3 crosstab... they columns don't line up
properly.

To recap, the following data somehow should be grouped in some form
of a
matrix:
- State (currently 4 values)
- Questions (currently 3 fields)
- Answers (currently 24 values; 8 records * 3 fields)


Essentially, I'd like to show that "California" has e.g. 3 counts of
"To
some extend" and "5 counts of some other value".
And somehow indicate under which questions these counts occur.

If you feel comfortable downloading the sample db and spreadsheet,
this
all
will make much more sense.


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine Crosstab Queries? 2
dcount in a crosstab query 3
Crosstab, Quarter Sum isn't working 6
Crosstab query with unique count 6
Union Queries 1
Group by Range in Crosstab Query 5
Crosstab with multiple fields 2
CrossTab Query question 1

Top