Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report writing (Access 97)

    I have a database that is being used to collect survey data. No problem....well...sorta. Each survey question can have three answers and there are drop-down list of the 10 choices. A user could answer one question with one answer and someone else may pick three from the list. I have set up the table with 3 fields.

    How can I generate a report with a tally for each choice per question? Does that make any sense?

    Thanks,
    Deborah

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    In my survey applications there are 3 tables: tblSurvey (Survey Header information), tblQuestion (the Survey questions) and tblAnswer (holds the tblSurvey.SurveyID, the tblQuestion.QuestionID and the respondent's answer). In order to tally responses, I count and calculate on tblAnswer for a particular QuestionID.

    I believe that even though your questions have a maximum of 3 possible answers, that structure will still work. That is; the same Survey will not have more than one identical response to the same question.

    I would not recommend that you structure your Answer table with 3 fields for the 3 answers as you have suggested. It is not normalized and will be prove difficult to tally answers when they could reside in more than one field.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Report writing (Access 97)

    The way you have set it up, you could run a unioin query that first got one field, then the second then the third. You could then build a grouping query on that.

    Another option is to move the answers to the questions to a separate table from the questions, (with a 1 - many relationship) then you could store any number of response in the same field.
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    Thanks for the reply. You know that Access is like everything else - if you don't use it - you will lose it. I have currently switched from the computer training field to corporate training and I feel like I don't remember anything. Well...I understand your logic, however, I am unsure about what is in your question table. I know questions but how is it built. Is it possible to send me a sample of your survey database or of the relationships. I am more of a visual learner and I need to see it to get a clearer idea of what to do.

    Thanks for all your assistance,

    Deborah

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    I had previously posted a sample survey structure in This Post.
    The key on the tblResponse (I referred to it as tblAnswer before I saw this sample) needs to be expanded for your purpose because the same QuestionID can have more than 1 record per SurveyID. Possibly all three fields?

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    I can not preview the attachment because I have access 97.

    I will view the old post information and see what I can come up with.

    Thanks,
    Deborah

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    If someone can tell me how to include a picture of the Db's relationship window, I think that might help you understand how it is structured.

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    You can do a screen print and paste in Word and then attach. I have not tried to screen print and paste in the forum.

    Looking forward to seeing the printout.

    Deborah

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    I attached it as a zipped Word document which contains the Db Relationships window.
    HTH
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    I know you must think I am as dumb as dirt but I am having difficulty with the concept you gave me. I went home and opened the database and converted it to an earlier version so I can at least look at it. I added answer 1, answer 2 and answer 3 to the tblresponse table. Is this correct? Do I put all my questions in the tblquestion table?

    When you generate a form so the user can add data, did you create a query of all fields? I have drop-down list for the user to select an answer - is this still going to work?

    I can not see the whole picture and I am having problems with the form.

    HELP!!! I don't think it is only the state of SC in a drought - my mind is had dried up!


    Thanks,
    Deborah

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

    Re: Report writing (Access 97)

    The response table should hold the actual answers selected, not your proposed answers. To use comboboxes on your form you would need a table holding the ProposedAnswers along with something like the Question ID so that you could query out the proposed answers by question. The selected value would be the one inserted into the Reponses table.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report writing (Access 97)

    Not at all ... you're just trying to understand.

    I would not recommend that you change the structure of tblResponse that way (Answer1, Answer2, Answer3) because that is not normalized. I am saying that if SurveyID 1 chooses 3 Answers (let's say 25, 35 and 45) for Question 6. then tblResponse would have 3 records like this:
    <pre>SurveyID QuestionID Answer
    1 6 25
    1 6 35
    1 6 45</pre>


    Then a respondent can choose as many answers as you permit per question and your statistics can be calculated from tblResponse per QuestionID.

    I do put all the questions in a tblQuestion like this:
    <pre>QuestionID QuestionText
    1 text of the first question
    2 text of the second question
    3 text of the third question</pre>


    The data entry form is unbound, (yes, you can use dropdown boxes) and I use code to write the 3 fields (SurveyID, QuestionID and Answer) to tblResponse. A new record is written for each Answer.

    Although it takes a while to code the unbound data entry and edit forms, it has served me well through numerous types of survey applications. Is that clearer?

Posting Permissions

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