Re: Crosstab queries

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" <anynomys@xxxxxxxxxxxxx> wrote in message
news:OAz5iwboFHA.1464@xxxxxxxxxxxxxxxxxxxxxxx
>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 Spread*** "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 spread***, this
> all
> will make much more sense.
>
>
> Thanks,
> Tom
>
>


.

Loading