Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count in query (2000)

    Hi,

    Current I am working on a db have answers of some questions and the answer always is Yes, No, N/A. I would like to run a report by counting how much answer is yes in each question. I build a query and use count function with "Yes" criteria. But the result always come total of answers that include (Yes, No, N/A.) and the criteria is not working. I only need count the total number "Yes" answer of each question.

    Please advice.

    Thanks

    Regards

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count in query (2000)

    Would you like to post the query here so we can look into the problem.

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

    Re: Count in query (2000)

    I assume you have a Totals query.
    Add the following expression:
    <code>
    CountYes: Abs(Sum([FieldName]="Yes"))
    </code>
    Replace CountYes with the name you want this column to have, and FieldName with the name of the field.
    Set the Total option for this column to Expression.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count in query (2000)

    Hi Hans,

    Thank you so much. It's works and count is correct. But do you mind explain more about this expresion, what's "Abs?" and why have to sum the field first? So I can total understand this expression.

    Thanks

    Regards

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

    Re: Count in query (2000)

    The expression [FieldName]="Yes" returns either True = -1 or False = 0.
    So if you sum [FieldName]="Yes", each record for which the condition is True contributes -1 to the sum, while records for which the condition is False contribute 0.
    In other words, the sum is -1 times the number of records for which the condition is True.
    The Abs function changes negative values to positive values.
    You could also use Sum(Abs([FieldName]="Yes")) but that is less efficient, since Access has to change each individual -1 to +1. Removing the minus from the sum is less work.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count in query (2000)

    Thanks, Hans. Now I am totally understand how to use this expression.

    Another quick question, I would like show "50/100" on the report. Current I create a label "/" and put between those two fields (50 & 100). But if the number changes to 1000, so "/" won't disappeared. Also I tried input Mask and seems like no works too. So is any better way to give to me what I want?

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

    Re: Count in query (2000)

    You can use one text box with a control source like this
    <code>
    =Abs(Sum([FieldName]="Yes")) & "/" & Count([FieldName])
    </code>
    Of course, you must replace FieldName with the name of your field.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count in query (2000)

    Thanks, Hans. It's works for me.

    Regards

Posting Permissions

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