Re: Crosstab queries

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

--
Duane Hookom
MS Access MVP
--

"Tom" <anynomys@xxxxxxxxxxxxx> wrote in message
news:OzQgNmdoFHA.708@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>> >
>> >
>>
>>
>
>


.