Re: Crosstab queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <anonomys@xxxxxxxx> wrote in message
news:%23WftLHfoFHA.1372@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Quantcast