Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Questionnaire - Design Question (Access 2000)

    An Access novice here with a table design question: We currently have in place an Access

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

    Re: Questionnaire - Design Question (Access 2000)

    Don't even try to store the questionnaire responses in a flat table like that. You'll never be able to give the director the information he wants out of it. Instead, here's how I would set it up. This would allow you to have multiple answers to a single question for each client and your task of reporting on the answers would be greatly simplified.
    Attached Images Attached Images
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questionnaire - Design Question (Access 2000)

    Many thanks, Charolotte! I appreciate the sample table designs. I tried them and they work well for query purposes. Is there a way, though, to indicate the options available for the answers to each question, e.g., a dropdown list box with the choices, yes/no, etc. We have many questionnaires to input and we don't want to take a chance on inputting typos, etc. Appreciate any advice you can give me in this respect. Thanks again....Mary

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Questionnaire - Design Question (Access 2000)

    Since Charlotte is off-line at the moment, I'll step in. It is fairly easy to limit the responses to a given question, and several questions can share a set of answers. One way is to design a form with a different combo box for each question, and make the RowSource for the combo box point to the appropriate set of answers (or you could embed them in the design of the combo box). Doing it that way you end up with a relatively complex form however. The other way involves designing a smart form (requiring some VBA) that changes the RowSource to match the question being answered. You end up with a simple form that has two controls: one displays the question (or question number) being answered, and the other, a combo box, displays the allowable answers. I presume in some cases you may want to allow the respondent to add some free-text notes to their response, in which case you might want to capture that too. Hope this helps.

    After reading your original question more carefully, I might also suggest the use of a multi-select list box for those questions where there can be multiple answers to a question. That would also involve some VBA, as the table design Charlotte suggested (a very appropriate one), would need to create multiple records based on the number of items selected in the list box. There have been several threads about the use of multi-select list boxes in the recent past.
    Wendell

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

    Re: Questionnaire - Design Question (Access 2000)

    To start with, build a form. Don't try to enter data into the tables themselves. With a form, you can control the input to a degree impossible with a table or query. Wendell has given you some good advice in that regard.

    I spent several years building this kind of thing for a marketing company, and it is NOT a simple process although it always looks to managers like it should be.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questionnaire - Design Question (Access 2000)

    Thank you very much, Wendell and Charlotte...I'm glad to know that I'm not the only one who thinks this "questionnaire results" database might be a bit more complex than not. I was thinking that I might create a "lookup" list for the "Response" field that would contain all possible answers to all questions in the survey--at least that would assure no typos in the data so that query results will be accurate. Actually, with just the 10 questions, the lookup list might only contain ~25 entries. Once again, I really appreciate you guys coming to my rescue so quickly. I continue to learn valuable information from you and the others in Woody's Lounge....Mary

Posting Permissions

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