Unanswered: Survey Database - select all that apply
I have been scouring the forum for a couple of days and can't find an answer for my question. I apologize if this is somewhere - please direct me there if you know where to find it.
I am creating a small database for one survey that we give to everyone who attends our programs. There are only 3 questions. We provide a list of answers with an other, in case they need a different scenario. Two of these questions are check all that apply and what are giving me a headache.
I created a survey question table, a respondent table, and an answers table. I tied them together with various IDs as instructed several places here.
Here is an example of what I have come up with so far:
I am trying to create a simple data entry form in which people can choose answers for some of the questions using a lookup in a combo box linking to answers that I choose from the answers table. I need to be able to enter more than one answer for some of the questions if the people have checked more than one answer. However, when I set this up it does not work. The combo box brings up a pull down that is empty.
I have already built this database using one table that utilizes look ups to fill in the answers but I know this is not the correct way to build a normalized database. I have seen many entries in here where people respond that if you have to be able to choose more than one answer than you need another table with a one to many relationship but I haven't seen any posts that explain how to do this.
If someone could explain what I have done wrong here and point me in the wrong direction, I would appreciate it.
You really shouldn't design tables using the q1,q2,q3 format. Same for the answers.
Consider something like this instead - where the table ID field is PK:
tblQuestion (QuestionID, QuestionText) - The list of questions
tblAnswer (AnswerID, QuestionID, Answer) - Possible answers for the question
tblParticipantSurvey (ParticipantID, QuestionID, AnswerID) - Which answer they chose
You just have multiple rows in the database for the answers and survey answers.
For the data in tblQuestions you might have:
11, How well did the forum meet your expectations
12, How did you hear about the forum (check all)
The data in tblAnswer (the allowable answers for a question) might look like this:
AnswerID, QuestionID, Answer
1, 11, Below Expectations
2, 11, Met Expectations
3, 11, Exceeded Expectations
4, 11, Other
5, 12, A Friend
6, 12, Coworker
7, 12, Advertisement
Then the Participant Survey table would look like:
ParticipantID, QuestionID, AnswerID
1, 11, 2 - participant one answered question 11 with 2 (Met Expectations)
1, 12, 5
1, 12, 6 - participant one answered question 12 with both answers 5 & 6 (friend & coworker)
can you provide any additional info on the issue you are having with the combo box?