Help with survey response query

  • Thread starter Thread starter apex77
  • Start date Start date
A

apex77

I am trying to query a survey repsonse table called tblMain. It look

like this:

Name Q01 Q02 Q03
Joe 3 5 6
Jim 4 4 4
John 5 3 3

I am trying to show the number of 3s, 4s, 5s for each question (Q01,

Q02, Q03) in a report, but am unsure about how to query the table,

WITHOUT having to create a seperate query for each response (there

are 14 questions in the real table).
Thank you
 
There are various ways to Count or Sum the values in each field (either in
the Report footer, or in the query, or with a subquery for each field), but
the real problem here is that the table design is wrong.

Whenever you see repeating fields such as Q01, Q02, Q03, ..., it always
means you need to have a related table, where one person can answer many
questions.

Duane Hookom has prepared an example of how to set up a normalized survey
database. You can download a copy to look at here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3
 
As Allen mentions, whenever you have columns labled Q01, Q02, ..., it
implies that your data is not structured properly, and to do what you want
with that structure can be complicated. To uncomplicate things, create what
many call a Normalizing Union Query. This query will take your data from
its current unnormalized format, and put it into a format that is easy to
analyze. The way you do this is to start a new query, but don't add a table
to the query grid. Go directly to the SQL view of the query. It should
look like "SELECT;" when you first open the SQL view. Now, use the following
to normalize the data (save it as qryNormalData:

SELECT [Name], "Q01" as QNum, [Q01] as RespValue
FROM yourTable
UNION ALL
SELECT [Name], "Q02" as QNum, [Q02] as RespValue
FROM yourTable
UNION ALL
SELECT [Name], "Q03" as QNum, [Q03] as RespValue
FROM yourTable
UNION ALL
....
SELECT [Name], "Qxx" as QNum, [Qxx] as RespValue
FROM yourTable

**Note: Name is a reservered word, and should not be used to name a field ,
table, or any other object in Access. If you want a list of other words
that should not be used for objects and fields, refer to Allen Browne's
website allenbrowne.com

You will need to insert SELECT statements followed by the UNION All
statement for each of the questions in your table. Note that the final
SELECT clause will not be followed by the UNION ALL statement. This should
return a result that looks like:

Name QNum RespValue
Joe Q01 3
Jim Q01 4
John Q01 5
Joe Q02 5
Jim Q02 4
John Q02 3
Joe Q03 6
Jim Q03 4
John Q04 3

Then, do get the results you were asking for in your original question, you
could simply write:

SELECT QNum, RespValue, Count(QNum) as Frequency
FROM qryNormalData
GROUP BY QNum, RespValue

You could also get Average and Standard Deviation values from this query,
where getting it from your original table would require you to write a
separate query for each question.

SELECT QNum, Avg(RespValue) as Average, StdDev(RespValue) as Deviation
FROM qryNormalData
GROUB BY QNum

HTH
Dale
 
EXCELLENT!!! Makes tons of sense. You guys have all been a great help. I am
in the process of writing the normalization UNION query and am on the right
track!

Dale Fye said:
As Allen mentions, whenever you have columns labled Q01, Q02, ..., it
implies that your data is not structured properly, and to do what you want
with that structure can be complicated. To uncomplicate things, create what
many call a Normalizing Union Query. This query will take your data from
its current unnormalized format, and put it into a format that is easy to
analyze. The way you do this is to start a new query, but don't add a table
to the query grid. Go directly to the SQL view of the query. It should
look like "SELECT;" when you first open the SQL view. Now, use the following
to normalize the data (save it as qryNormalData:

SELECT [Name], "Q01" as QNum, [Q01] as RespValue
FROM yourTable
UNION ALL
SELECT [Name], "Q02" as QNum, [Q02] as RespValue
FROM yourTable
UNION ALL
SELECT [Name], "Q03" as QNum, [Q03] as RespValue
FROM yourTable
UNION ALL
....
SELECT [Name], "Qxx" as QNum, [Qxx] as RespValue
FROM yourTable

**Note: Name is a reservered word, and should not be used to name a field ,
table, or any other object in Access. If you want a list of other words
that should not be used for objects and fields, refer to Allen Browne's
website allenbrowne.com

You will need to insert SELECT statements followed by the UNION All
statement for each of the questions in your table. Note that the final
SELECT clause will not be followed by the UNION ALL statement. This should
return a result that looks like:

Name QNum RespValue
Joe Q01 3
Jim Q01 4
John Q01 5
Joe Q02 5
Jim Q02 4
John Q02 3
Joe Q03 6
Jim Q03 4
John Q04 3

Then, do get the results you were asking for in your original question, you
could simply write:

SELECT QNum, RespValue, Count(QNum) as Frequency
FROM qryNormalData
GROUP BY QNum, RespValue

You could also get Average and Standard Deviation values from this query,
where getting it from your original table would require you to write a
separate query for each question.

SELECT QNum, Avg(RespValue) as Average, StdDev(RespValue) as Deviation
FROM qryNormalData
GROUB BY QNum

HTH
Dale

apex77 said:
I am trying to query a survey repsonse table called tblMain. It look

like this:

Name Q01 Q02 Q03
Joe 3 5 6
Jim 4 4 4
John 5 3 3

I am trying to show the number of 3s, 4s, 5s for each question (Q01,

Q02, Q03) in a report, but am unsure about how to query the table,

WITHOUT having to create a seperate query for each response (there

are 14 questions in the real table).
Thank you
Click to expand...
Click to expand...
 
Back
Top