Counting Check Boxes Query

This tip Counting Check Boxes Query is suitable for all versions of Microsoft Access

The counting of check boxes query in Microsoft Access had me puzzled for some time when I first started with Microsoft Access. How do you count Check Boxes (Yes/No data types)? – Have any of you ever tried this. A result is recorded for all check boxes whether they be positive or negative, Yes or No, checked or unchecked.

The values that are stored in check boxes are -1 for Yes and 0 for No. The problem therefore occurs when you wish to count how many Yes answers there are and/or how many No’s, as both answers are counted.

If you used the Count function, the total count of all the records would be the result – as there is a value recorded for this data type in all records. You can, of course, do it in a few queries by filtering the entire Yes answers and counting them and then all the No’s and counting them, but it is a bit messy – and does not solve the problem of counting them in a form or a report.

One way to get around this is to use the Sum function instead. This works well for Yes answers as it will add all the -1 resulting, in the number of Yes answers, albeit as a –ve number. To change into a positive number, multiply the result by –1, when required.

This does not solve the problem for No answers. For this you would have to count all the records (check boxes) and add the Sum of Yes’s – remember the Sum of Yes’s are –ve, therefore this is the same as subtracting a positive number.

The formulas would look like this (just change the ‘NameOfCheckBox’ to the name of your check box).

Add this to the first row in your query:

Yes (checked):

CountOfYes:Sum(NameOfCheckBox)*-1

No (not checked):

CountOfNo:Count(NameOfCheckBox)+Sum(NameOfCheckBox)

If you are doing this in a form or a report, you will need to add an = sign before the formulas and omit all before and including the :

It works best if you add this to the report footer or header.

Return from Counting Check Boxes Query to Home Page

or

Return to Microsoft Access Queries