# Thread: Count in query (2000)

1. ## 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. ## Re: Count in query (2000)

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

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•