Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Counting yes responses (XP)

    Hi,

    I'm working on a database that has 10 questions with responses of either "met", "not met", "UTD" or "NA". I am building a report based on these questions. I need to know the following data:
    1. Total number of responses for a question
    2. Total number of responses per catagory listed above (met, not met, UTD or NA)
    I've been trying to obtain the info in a query but am not sure how to write the formula to count the responses.

    Thanks!

    Leesha

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

    Re: Counting yes responses (XP)

    You can do this directly in the report.
    Create a report based on the responses table in design view.
    Select View | Sorting and Grouping.
    In the Sorting and Grouping window, add the field that identifies the questions, and set the Group Footer property to Yes.
    If you don't want to report the individual responses, leave the Detail section empty and set its height to 0.
    Create 5 text boxes in the Group Footer. Set their Control Source to:

    <table border=1><td>Total responses</td><td>=Count(*)</td><td>Met</td><td>=Abs(Sum([Response]="Met"))</td><td>Not met</td><td>=Abs(Sum([Response]="Not met"))</td><td>UTD</td><td>=Abs(Sum([Response]="UTD"))</td><td>NA</td><td>=Abs(Sum([Response]="NA"))</td></table>
    Replace 'Response' by the name of the field that contains the response.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Thanks Hans! I will get to work trying this. I'm determined NOT to take the easy way out and send it to Excel to analyze!

    One more quick question on the same report. I have comments that go with each question. In many cases the comment field is blank. I need to create a report that shows the comments, however I don't want to have blank spaces where there is nothing documented. Is there a way to only show comments that contain data?

    Thanks again,
    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Sorry, responded to my original post. Wanted to be sure you got this.

    Thanks again,
    Leesha

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

    Re: Counting yes responses (XP)

    Set the Can Grow and Can Shrink properties of the text box bound to the comments field to Yes, and also for the section containing the text box.
    Note: Can Shrink and Can Grow only affect the height of the text box and section, not the width.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Ok, I'm getting there. On the first question - I set the details height to zero as you suggested and that works great. The numbers that are being reported in the foot are correct. However, rather than report all of the numbers in one section, it is breaking them out separately. There is a separate set of grids for each of the 4 responses with a total for only one answer at a time. IE

    Met 19
    Not Met
    UTD
    NA

    Met
    Not Met 4
    UTD
    NA

    Met
    Not Met
    UTD 1
    NA

    I'd like all the answers to be in one grid.

    As for the shrinking and growing question. I set the properties as you instructed however I still have gaps between answers which I assume are the responses that were blank. Both Can shink and Can Grow are set to yes.

    Thanks,
    Leesha

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

    Re: Counting yes responses (XP)

    You have apparently set the grouping field to the response field. You must either group on another field (you'll have to decide for yourself, or experiment) or move the text boxes to the report footer section and delete the grouping level altogether.

    The labels prevent the text boxes from moving up. Perhaps you should arrange the text boxes horizontally instead of vertically.

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Hi Hans,

    Thank you for your patience on this. I think I'm lost. Either that or I wasn't clear in my description of the problem(s). Let me just tackle one at a time. The biggest issue is with the grid for the Met, Not Met, UTD and NA answers. I've set the report up to have the response to the quesion in the question footer as you instructed. The grid however doesn't show all of the data in one grid but breaks it out into separate grids. I'm attaching a .pdf file to show you how it is coming out. I need all the data in one grid.

    Thanks,
    Leesha
    Attached Files Attached Files

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

    Re: Counting yes responses (XP)

    Apparently, there is some other kind of sorting or grouping going on - I would guess on response. You will probably have to remove a sorting or grouping level. If you make a backup copy of the report, you can experiment safely. If you can't solve it, you might post a screenshot of the Sorting and Grouping window for the report, or post a stripped-down copy of the database, so that someone can look at it directly.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Here's a stripped down version of the database.

    Thanks!

    Leesha
    Attached Files Attached Files

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

    Re: Counting yes responses (XP)

    The structure of the table is different from what I had thought (I expected each question to be in a different record.)

    As I wrote before, the text boxes must be moved to the report footer section and the grouping level (on Question 1) removed. If you want to display the results for Question 2 etc. too, you must duplicate the set there is now and adapt it for Question 2 etc. If you want an overall count over all questions, you will have to add the results together, e.g.

    =Abs(Sum([Question 1]="Met"))+Abs(Sum([Question 2]="Met"))+...+Abs(Sum([Question 10]="Met"))

    This is due to the structure of the table.

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

    Re: Counting yes responses (XP)

    Oops, here is a modified version (I stripped out a lot more!)
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Thanks so much!!! As always you are a wealth of information and soooooooooo patient!

    Leesha

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting yes responses (XP)

    Back again. One more question (I hope). I need to do the same thing with a question that has a Yes/No control. I need to find the total number of "yes" responses. I tried

    =Abs(Sum([Further Action Needed]="yes"))

    but I get an error message stating "Data type mismatch in criteria expression".

    Thanks,
    Leesha

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

    Re: Counting yes responses (XP)

    A Yes/No field actually doesn't store "Yes" or "No" as text values, but True (= -1) or False (= 0). Try this:

    =Abs(Sum([Further Action Needed]))

Page 1 of 2 12 LastLast

Posting Permissions

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