Re: Crosstab queries

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" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:OoF0dAeoFHA.4028@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>>> >
>>> >
>>>
>>>
>>
>>
>
>


.