Creating Basic Survey With Multiple Answers

[Kermit]

I am VERY new to access, and I am trying to use it to analyze that data from
a survey. One questions asks you to pick 3 from a selection of 7 possible
choices. What I want to get from this is a pie chart that has the numbers of
"hits" on that answer compared to the others.

I currently have that data in 3 separate fields under the datasheet.
(Choice 1, choice 2, choice 3) and dropdown menus for the possible selections.

 

[Kermit]

Let me see if I can explain this a little better.

For example... The question might be pick the 3 best forms of communication
media.
Selections are A. CD, B. Mail, C. Email, D. Phone, E. Direct, F. Video/TV

The problem that I'm having is that I would like to show this data in a pie
chart, comparing the percents of each selection. The problem is that the
way that I have it entered (as three separate fields), I'm not able to create
a pie chart of the totals of all three columns.

The best I can get is a pie chart of one column at a time...

Make sense?

 

[Kermit]

Please, I really could use some help here... someone must know what I'm
taking about or can point me in the right direction.

Could the Formulas>Create Calculated Total or Formulas>Created Calculated
Detailed Field help at all here?

No matter what I try, all of my formulas depend on the selections...

ie: Shows % of people that picked CDs> then Letters> then TV compared to
CDs> then TV, then Letters.

Order is not important here, so what I really want is sum of all TV choices
compared to sum of all CDs compared to sum of all Letters.

 

[Douglas J. Steele]

See whether Duane Hookom's "At Your Survey" helps.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[Ken Sheridan]

You've made the common mistake with this type of application of having
separate columns for each answer. As you've realised this is the source of
your problems in aggregating the data.

A correct design would be to store the question and answers in separate
tables. In the Answers table you'd have a column referencing the primary key
of the Questions table, QuestionID say and an Answer column, so with a
question with 7 possible answers you'd have 7 rows in the Answers table.

You'd then have a Responses table with columns RespondentID, QuestionID and
Answer. This table would be related to a Respondents table containing the
respondents' names etc and to the Answers table, the last relationship being
on the two columns QuestionID and AnswerID, with referential integrity
enforced so an incorrect question/answer combination can't be entered. If
you want to indicate priority then you'd also need a Priority column in this
table with possible values 1 to 3.

It is possible to recast your current data into this form using a number of
append and update queries, and I'd recommend you consider doing this. You
can achieve your current aim with the existing table, however, by using a
UNION query to return a single result set with one column for the answers.
You'll need to do this in SQL view; it can't be done in query design view,
but the query would look like this:

SELECT [Question], [Choice 1] AS Answer
FROM [YourTable]
UNION
SELECT [Question], [Choice 2]
FROM [YourTable]
UNION
SELECT [Question], [Choice 3]
FROM [YourTable];

Save this query as qryAnswers say. Then base another query on it which
groups by Question and Answer and counts the rows for each. In SQK view it
would look like this:

SELECT [Question], [Answer],
COUNT(*) AS AnswerCount
FROM [qryAnswers]
GROUP BY [Question], [Answer];

You can then base a chart on this query. If you want to restrict it to a
single question you can do so:

SELECT [Question], [Answer],
COUNT(*) AS AnswerCount
FROM [qryAnswers]
WHERE [Question] = "What three forms of communication would you prefer?"
GROUP BY [Question], [Answer];

Ken Sheridan
Stafford, England