Design help for capturing survey results

[Joanna]

I have to construct a database to record survey results. The questions are
wordy, but the answers are primarily yes/no or numeric.
My challeng is to set up the tables efficiently. My initial thought was to
create tables that would use a crammed-in version of the question as the
field name, and just set the fields to Yes/No or numeric where applicable.
I'm really not confident that that is the most efficient way to set it up,
though, because I was always taught to keep field names brief, and that seems
like a very clunky way to do it.
The only other alternative I can think of is to number the questions in a
lookup table, and use the number as a question ID that would link to the
response table. That would work, but may make it more confusing for the next
person to manage the database when I move on.
Any thoughts on this?? Hopefully I'm overlooking an obvious simple solution
(??) Thanks!

 

[Allen Browne]

The basic structure would be these 4 tables:

Question table (one record per question):
QuestionID Number primary key
Question text of the question

Answer table (one record for each possible answer to each question.)
QuestionID relates to Question.QuestionID
AnswerNum number of this answer
Answer text of the answer
(QuestionID + AnswerNum = primary key)

Repondant table (one record for each person who takes the survey)
RespondantID primary key
Surname Text
FirstName Text
SurveyDate Date/Time (when they took the survey)
...

Response table (one record for each answer given.
ResponseID AutoNumber primary key
QuestionID which question this person is answering
AnswerNum which answer they chose
Comment anything else they said in answering this question.

In the Answer table, there will mostly be 2 possible answers to each
question (yes or no.)

 

[Joanna]

I just found Duane's "At Your Survey" link....
panicked first, researched second
It looks like it will be a huge help.

 

[Joanna]

Thanks - that is exactly what I was looking for.

The basic structure would be these 4 tables:

Question table (one record per question):
QuestionID Number primary key
Question text of the question

Answer table (one record for each possible answer to each question.)
QuestionID relates to Question.QuestionID
AnswerNum number of this answer
Answer text of the answer
(QuestionID + AnswerNum = primary key)

Repondant table (one record for each person who takes the survey)
RespondantID primary key
Surname Text
FirstName Text
SurveyDate Date/Time (when they took the survey)
...

Response table (one record for each answer given.
ResponseID AutoNumber primary key
QuestionID which question this person is answering
AnswerNum which answer they chose
Comment anything else they said in answering this question.

In the Answer table, there will mostly be 2 possible answers to each
question (yes or no.)

 

[Allen Browne]

Yes, that's a good example.

I should have linked that for you, so pleased you found it.