How to Count Check Boxes that are checked Yes in an Access 2003 Report

Posted on 2008-11-03
Last Modified: 2013-11-28
I have a table that has 10 different check boxes for various options.  I need to create a report that shows the total number of times each check box is selected.    Some records can have multiple check boxes selected  and other records might not have any check boxes selected.  
(Examples of field names for the check boxes are, patient_reffer_family, patient_reffer_insurance, patient_reffer_friend)

I also want to show a total number of records that have at least one of the available check boxes selected.   I want to use that number to figure out percentages based on the totals from the individual check box totals.

I beleive I need to use the count feature, but I am not sure how to code this properly  If possible, examples of how to use this code would very helpful.
Question by:anctech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 44

Expert Comment

ID: 22868763
A checkbox has a real value of either -1 or 0

to count by record, using dummy 'short' names

SELECT-(cb1+cb2+cb3+cb4+cb5+cb6+cb7+cb8+cb9+cb10) as CountofCheckboxes FROM myTable:

LVL 44

Expert Comment

ID: 22868776
There should be a space:

SELECT -(cb1+cb2+cb3+cb4+cb5+cb6+cb7+cb8+cb9+cb10) as CountofCheckboxes FROM myTable:

Author Comment

ID: 22869045
Thanks, but you lost me.    That looks like it would count all the checkboxes.  Also, where would that statement go.

How would I make a control directly on the report?
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 22869677
Put it in the control source of a text box, provided the report is bound to the table containing the checkboxes amongst other things:  

= -(cb1+cb2+...+cb10)

Remember, this is reversing the sign of the sum (not count) of the value of all the check boxes, some of which are 0 (False) and some are -1 (True).  You do not want to use the Count() function.  In fact you are not using the Sum() function either - you are simply adding 10 fields, each of which has a value of 0 or -1, and changing the sign of that sum.  Get it?

Author Comment

ID: 22869839
You are one step ahead I think.  I need to get a total for each individual check box first.   After I get that, then I can use your example to get a grand total.

Here is what I tried to get a count of just one single check box field.

I tried this  to get a count for one of the check boxes and it didn't work.
  Sum -([patient_reffer_insurance])

If I use   Sum ([patient_reffer_insurance]) without the - sign, I get a result of -7.00

I need this to be a postive number
LVL 44

Expert Comment

ID: 22870696
You would use -Sum(patient_refer_insurance)  - spelling reffer?  Thanks, glad to help.
LVL 44

Expert Comment

ID: 22870719
Don't like the B though.  Why did you close the question when you still had one?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question