Count in query (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

I assume you have a Totals query.
Add the following expression:

CountYes: Abs(Sum([FieldName]="Yes"))

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

You can use one text box with a control source like this

=Abs(Sum([FieldName]="Yes")) & "/" & Count([FieldName])

Of course, you must replace FieldName with the name of your field.

Reply | Quote

Thanks, Hans. It’s works for me.

Regards

Reply | Quote