MS Access question?

Question

In Access, what is the expression to use when trying to display in a form the total number of "yes" or "no" in a column from a query?

Answer 1

Look into the COUNT() function.

If that doesn't work for you then just make your own counters--scroll through the column and add 1 to counterYes or counterNo as appropriate.

Answer 2

To answer your question, I'll make the assumption that you are displaying table records in a continuous form. If this is not the case, and the following doesn't suit your situation, post back a response and I'll adjust my solution.

Here's what you need to do. In my example, I'll call the box you want to count Response. I'll call the table source Invitation. Let's say you want to count the number of "Yes" responses and display the total at the bottom of the form.

1. Put a Text Box in the Form Footer. You'll be putting a DCount expression in this box that will count the number of checked boxes.
2. In the Control Source property of the Text Box, type the following:

=DCount("[Response]", "Invitation", "[Response]=Yes")

In the DCount function, the first argument represents WHAT you are counting, the second argument is the TABLE/QUERY where the field you are counting is located, and the third argument represents under what CONDITIONS should you count the item.

Of course, substitute the real names of your table and field in the above expression. Make sure you include the quotation marks as well. If you'd like, you can make another Text Box on your form to count the number of No responses in the same manner.

Hope this helps!

Answer 3

You can do this through the Query Builder or through SQL.

SQL first, here is the statement that sums the total number of records in the table "TheTable", where the value of "TheField" is yes (true).

SELECT Count(TheField) AS Total
FROM TheTable
HAVING TheField=True;

To do this in the Query Builder, add the table to the query, then add the field that you are using into the query twice. In the first field set the Criteria to yes or true.
Then you must click on the Totals button (large Sigma on the toolbar), this will make the Total: row show up in the grid. For the second field, change the Group By to state Count.

You should now be able to run the query and get the results that you are looking for.