Re: Crosstab queries

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" <duanehookom@xxxxxxxxxxxxxxxxxx> wrote in message
news:OVst#BcoFHA.4012@xxxxxxxxxxxxxxxxxxxxxxx
> 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