Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tally Survey Results (2003, SP2)

    I have imported some survey results. There are 10 questions, each question is a field. The answer to each question could be from 1 - 7. I am looking for a way to design a query where I get a count of 1', 2's,3's for each of the 10 questions. Is there an easy way to do this in Access? It's easy to do one question, but what about 10?
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    It would be much easier if you had a separate record for each question, tallying would then be a simple matter of designing a totals or crosstab query.

    If you want to keep the current structure, you'll probably have to create a query for each question., then create an 11th query that combines the results.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    Thanks for the advice. I found this MS KB article, which has some good info, too. http://support.microsoft.com/?kbid=292299

    I noticed on the expression used for the query MS used curly brackets }} instead of regular )) so if anyone else uses the KB article, make note!
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    > MS used curly brackets }} instead of regular ))

    What a strange typo! The article was probably typed in by someone who doesn't know Access, and then they forgot to proof it...

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    I've got my first "draft" working, except for in the Query Results the tally shows with negative numbers. Is there an easy fix for that?
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    Could you post the SQL for your query? Or post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    Thanks for looking at this...
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    The result of an expression such as [Result]=1 is either True = -1 or False = 0. If you sum the expression, you therefore get -1 times the number of results equal to 1. To convert to a positive number, use the Abs function, e.g.

    VS: Abs(Sum([response]=1))

    But it is also possible to use a crosstab query to obtain the same results. In the attached version, I have added a table that provides the "code" (VS, SD, etc.) for each response, and created a crosstab query that counts the responses.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    I see my error now. That makes sense, thank you.

    I have a question about using the crosstab query. I don't know the expressions so I was trying to duplicate your results using the crosstab query wizard. I got the same results, plus added the "total" number of answers, but the "codes" (vs, s, etc.) are not in the original order. They go from very satisfied to less satisfied, so I want them in the original order, like yours was. Is there a way to accomplish this? I don't see anything in the wizard, which I'm guessing is why you didn't use it!!!
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    You can specify which column headings are shown, and their sort order, as follows:
    - Open your crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Click in the Column Headings property.
    - Enter the column headings in the desired order, separated by semi-colons.

  11. #11
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    That's exactly what I wanted. It's a little work getting everything over from the survey software and getting it arranged the way it needs to be. But now that I have the framwork...

    I have learned so much. Thank you for helping me and allowing me to learn from it. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    egghead

  12. #12
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tally Survey Results (2003, SP2)

    I am using Question ID for the row headings. The question ID's are being sorted alphabetically. I want to sort them in the order the question appeared on the original paper survey. Is it possible to do a custom sort order? I went into the table properties and changed the Index property to "no", but it didn't affect the query. Any ideas?
    egghead

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

    Re: Tally Survey Results (2003, SP2)

    You should create a number field that specifies the order (first question = 1, etc.). You can then sort the crosstab query on this field.

Posting Permissions

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