customer survey summary

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good evening - we have a customer survey which contains six questions; the
answer options for the first three questions are yes, to some extent, no and
don't know. The answer options for the last three questions are excellent,
good, fair and poor. I'm trying to summarize how many customers answered
with each option for each question. I would also like to convert the summary
into a graph. I have the table set up a couple of different ways; one is
composed of a single table with drop down menus containing the answer
options, the other has two tables - the answer options in drop down menus in
the main table are in a second table with two columns (col1 = yes, to some
extent... col2 = excellent, good...) Any ideas?
 
If you want to take a look at a FREE tool that handles surveys and allows you to
create your own easily take a look

Duane Hookom has a sample survey database at

http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

This fully functional application uses a small collection of tables, queries,
forms, reports, and code to manage multiple surveys. Users can create a survey,
define questions, enter pre-defined answers, limit to list, report results,
create crosstabs, and other features without changing the design of any objects.
 
To do the analysis you need a table showing answers to questions that looks
like:
Question
Answer
According to database Normalization rules, you don't want to enter the
literal question and literal answer in each record so you need a table for
the literal questions and a table for the literal answers. Noting that some
literal answers only are appropriate for questions 1 to 3 and the other
literal answers are appropriate for questions 4 to 6, you need a table to
identify the type of answer and which questions the type is appropriate for.
So -------
TblQuestion
QuestionID
QuestionNum
Question
AnswerTypeID

TblAnswerType
AnswerTypeID
AnswerType '1 for yes, to some extent, no and don't know. 2 for
excellent, good, fair and poor.

TblAnswer
AnswerID
AnswerTypeID
Answer

TblAnswerToQuestion
AnswerToQuestionID
QuestionID
AnswerID

If you want to keep track of which survey the answers were on, then you need
another table, TblSurvey, and you need to add SurveyID to
TblAnswerToQuestion.

Your data entry form must fill in TblAnswerToQuestion. Since the fields are
both foreign keys, you need a combobox for each field.
For QuestionID ---
Create a query based on TblQuestion and include the fields QuestionID,
QuestionNum and AnswerTypeID in that order. On your form add a combobox
named CbxQuestionID and set the row source property as the query you just
created. Set the bound column as 1, Column Count as 3 and Column Widths as
0;.5;0.

For AnswerID ---
Create a query based on TblAnswer and include the fields AnswerID, Answer
and AnswerTypeID in that order. Enter the following expression in the
criteria of AnswerTypeID:
Forms!NameOfYourForm!HiddenAnswerTypeID
On your form add a combobox named CbxAnswerID and set the row source
property as the query you just created. Set the bound column as 1, Column
Count as 3 and Column Widths as 0;1.5;0.

On your form, add a textbox named HiddenAnswerTypeID and set its Visible
property to No.

Go back and select CbxQuestionID. Add the following code to the AfterUpdate
event:
Me!HiddenAnswerTypeID = Me!CbxQuestionID.Column(2)
Me!CbxAnswerID.Requery

If you select question 1, 2 or 3 in the question combobox, you will only see
yes, to some extent, no or don't know for answers in the answer question
box. If you select question 4, 5 or 6 in the question combobox, you will
only see excellent, good, fair or poor for answers in the answer question
box.

You will do your reporting and graphing on queries that include
TblAnswerToQuestion and other table(s) as needed.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
Back
Top