Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a survey DB (2002)

    Hi,

    I need to create several survey databases and input the data based on paper surveys/questionnaires that have been filled out. The input methods on the paper survey are varied in that some questions are short answer, others are multiple choice and others are possibly Y/N, T/F answers.

    I would like to display the entire survey on a form so that the input can be done one survey at a time. The only way I know of to do this is to create a table where the fields are the questions themselves. I realize this violates first normal form repeating groups of fields not to mention destroying my reporting and querying ability, but if I do a one (question) to many (answers) relationship I am not able to display the entire survey with various input controls on the form as there are only 2 fields involved. The question field and answer field. I end up creating a form/subform where the main form contains the question and the subform contains the answers to that question. As a result I end up entering all the answers for question 1 then all the answers to question 2, etc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a survey DB (2002)

    This is a difficult situation. I can think of some more or less normalized solutions, but they all involve using some code.

    One way would be to use a Replies table with fields named (for instance) SurveyID, QuestionID, TextReply (for "open" questions), NumericReply (for multiple choice), BooleanReply (for Yes/No questions) and DateReply (for date/time questions). The Questions table would have a QuestionType field that specifies the type of reply to use for each question. You'd need code to create a full set of reply records when you start to enter a new survey, and you could also use code to display only the appropriate controls for a specific question.

    You could also work with separate tables for text replies, numeric replies etc., with keys to bind everything together. This would get complicated very fast. A solution would be to use classes, but that's advanced VBA stuff.

    Unless you really need to do cross-question statistics, I would stick with one question = one field.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a survey DB (2002)

    Thank you Hans,

    This puts my mind at ease, regarding complexity, as I was thinking that there was a simple end user solution looking me in the face and for some crazy reason, I wasn't seeing it.

    Best to you,
    Steve

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a survey DB (2002)

    Steve,

    I created a customer satisfaction survey database a few years ago -- I ended up using a single table with one field per question (Memo for short answer, Yes/No for check boxes, Integer for mutiple choice, etc.). It's less-than-graceful from a DB-design (normalization) standpoint, but subsequent reports (e.g., one survey per page, statistics, etc.) were relatively easy to create.

    As Hans suggested, sometimes there's a trade-off between ease of development and DB elegance. I tend to strive for the latter (it's more educational and tends to make down-the-road enhancements a lot easier), but this project was an exception.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a survey DB (2002)

    On the other hand, doing any kind of complex analysis on the data is difficult if not impossible with that table structure. I used to do this for a living and I used fully normalized tables, but I also used an unbound form to display the questions and capture the answers. Printing out a survey is easy with a flat table if that's all you need to do or if you only need numbers like how many answered yes to question 5. It gets complicated fast for any more sophisticated results analysis.
    Charlotte

Posting Permissions

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