Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Red face 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:

    tblQuestions
    QuestionID (PK)
    Question1 (text)
    Question2 (text)
    Question3
    Question4...

    tblAnswers
    AnswerID (PK)
    ParticipantID (FK)
    QuestionID (FK)
    Answer1 (Y/N)
    Answer2 (Y/N)
    Answer3 (Y/N)
    Answer4...

    tblParticipant
    ParticipantID (PK)
    Name
    Email
    Newsletter (Y/N)

    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.

    Thanks!
    Last edited by bothhands; 07-19-12 at 01:49.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    a couple of thoughts...

    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
    tblParticipant(ParticipantID, etc....)
    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:
    QuestionID, QuestionText
    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?

    Steve

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •