Survey Table Structure

[Capo]

I am attempting to create an Access database that reflects a paper-survey. Basically, surveys were sent and have been returned and we now wish to enter the information into a DB for reporting purposes.

The survey is all multiple choice, free-text, or a combination thereof (example being this: "What animal is your favorite? A) Dog B) Cat C) Bird D) Other: __________ (free text)")

How would be the best (and proper) way to arrange the tables for such a task?

Thank you in advance!

 

[yogi]

use your questionnaire as your template for the table structure

 

[CraigDolphin]

This should at least get you started ...

Table: Surveys
Table: Sponsors
Table: Questions
Table: People
Table: PeopleAnswers

Surveys
SurveyID (Pk; Auto)
SurveyName (Text)
SponsorID (FK; Long Int)

Sponsors
SponsorID (PK; Auto)
SponsorName (Text)
SponsorAddressStreet (Text)
blah
blah

Questions
QuestionID (PK; Auto)
Question (Memo)

People
PeopleID (PK; Auto)
PeopleFirstName (Text)
PeopleSurname (Text)
Blah
Blah

PeopleAnswers
PeopleAnswersID (PK; Auto)
PeopleID (FK; Integer)
QuestionID (FK; Integer)
Answer (Text or Memo)

Now, if you have 'right answers' as in the case of an examination, you might also want a table of CorrectAnswers:

CorrectAnswerID (Pk; Auto)
QuestionID (FK;Integer)
CorrectAnswer (Text or Memo)

You may not need the Sponsors or Surveys table if this db is only ever going to be used for one survey and one client....but if you ever want to re-use it for another survey and/or another client then this would give you the room to grow.

 

[Capo]

This will only be for a one-time survey, so I am fine with doing it via the shorter method, as long as that will do the task. I am new to Access, so forgive me for my questions.

Just a few more...

So, with the short route I will only need tables "Questions," "People," and "People Answers," correct?

Questions
QuestionID (PK; Auto)
Question (Memo)

Ok, just want to make sure I'm understanding you correctly. tblQuestions would have only two fields. QuestionID, which will be set to an auto-number and which will also be the primary key.

Then, the field Question will house the actual questions I want to use in the survey, correct?

People
PeopleID (PK; Auto)
PeopleFirstName (Text)
PeopleSurname (Text)
Blah
Blah

What's the purpose of tblPeople? Is this to record the info of who is entering the data or is it connected to the questions somehow? If so, how?

PeopleAnswers
PeopleAnswersID (PK; Auto)
PeopleID (FK; Integer)
QuestionID (FK; Integer)
Answer (Text or Memo)

I assume tblPeopleAnswers is to record the actualy results of the survey?

Thanks for your continued help!

 

[CraigDolphin]

Your interprestation of the Questions table seems right.

People is to identify the 'people' answering your questions. Let's say Bob Smith takes your survey. His PeopleID # happens to be '3'. When you record Bob's answers is PeopleAnswers you need to know ...

A> Who answered the question? PeopleID = 3 (=> Bob Smith)
B> What question is this the answer for? QuestionID = 1 (=> What color are your eyes?)
C> What was the answer? Answer = A (=> 'Blue')

By recording all three components you can easily query out the responses to the questions, and you can also investigate trends in answers (eg, if a person responds x to question y, then are they more likely to repond z to question aa)

Since you give little to no information on how your survey design is to be utilized this gives you the ability to query however you want.

 

[Capo]

I'll elaborate a bit more:

This survey is essentially a study. Every single question is either Multiple Choice, Free-text, or a combination thereof (such as: "What is your favorite animal?" A)Cat B)Dog C)Other:________ ) - By the way, how would I set that up?

I have been doing some reading regarding table normalization and would like to try to do things as "proper" as I can. However, this is only a one-time deal regarding THIS survey and once all of the data is punched in, we want to be able to report on it. (such as, how many who live in New York like dogs best, etc.)

 

[CraigDolphin]

The structure I have given you will allow you to query in the way you have described.

As far as setting up your form I would use the same form to show once for each question, with a combo box to input any 'previously canned' answers which is not limited to the list (so as to enable you to enter any 'novel' responses to a question.)

Then I'd setup a table that exists solely to list possible 'pre-canned answers'.

Something like....

Pre_CannedAnswers
PreCannedID (Auto, PK)
QuestionID (FK)
Answer (Text)

The Answer would be the bit you store. For example, this might be the 'Dog' part of 'A) Dog'. Unless you have a burning need to know the actual 'A, B, C or D' part of the answer I think it would be unnecessary to store that bit. Remember, this table exists solely to populate a combo box on your form with pre-defined answers to a particular question.

Base your combo box list source on a query of the pre-canned answers table, using the QuestionID on the Form as a parameter criterion. This way, when you open the form to a new question the combo box automatically displays the pre-canned answers for that question only.

Now, bear in mind that this design is for an either-or response scenario. If you want to allow the respondent to input more than one answer to any question, then you'd need to add another table to your structure to deal with that one-question-to-many-answers scenario.