Expert: Scottgem - 3/19/2009

Question
Okay, I'm too on the "artistic" side to really get my head around the db structure for surveys.  I have a 24 question survey that users fill out on line (www.arguard.org), which gets emailed to me.  I need to enter the data into Access to analyze it and make reports to my superiors, but I just don't understand how to apply your suggested structure to my db.  I've set up these tables:
tblAnswers
AnswerID (PK Autonumber)
Answer (text)

tblQuestions
QuestionID (PK Autonumber)
Question (text)

tblResponse
ResponseID (PK Autonumber)
QuestionID (FK?)
AnswerID (FK?)
SurveyDate (Date/Time)

I've related the first two to the corresponding fields in tblResponse.  I've populated tblQuestion with the 24 questions (Age, Gender, etc.), including 20 questions that require a choice of one of five ratings (Very Satisfied to Unsatisfied). I've populated tblAnswers with the 30 possible answers (i.e. 1 = 18-35, 22 = Somewhat Satisfied, 30 = Seldom).

So, how exactly does this work?  I'm really confused about what data is entered into tblResponse.  Shouldn't there be one row for one survey result?  Ugh.  I'm a photographer and a writer and my boss is wanting this to work.  Please help.

Answer
OK, Since this is a survey that has no right and wrong answers you don't need tblAnswers. Also, the identification type questions shouldn't be in tblQuestions. What you should have is a Repondents table:

tblRespondents
RespondentID (PK AQutonumber)
Firstname
Lastname
Age
Gender

The question now os whether a respondent will take the survey multiple times or take multiple surveys. If this is a one shot deal then you can record survey date in tblRespondents. If not you need a response table that links the respondent to the set of answers. For a one shot deal your answers table should look like this:

tblAnswers
AnswerID (PK Autonumber)
RespondentID (FK)
QuestionID (FK)
Answer

If its not a one shot deal, then you record the respondent and date in tblResponses and then use the ResponseID as your FK in tblAnswers instead of RespondentID.

Your answers should be setup to have one record for each question for each response.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA