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

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.
anctechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
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:

0
GRayLCommented:
There should be a space:

SELECT -(cb1+cb2+cb3+cb4+cb5+cb6+cb7+cb8+cb9+cb10) as CountofCheckboxes FROM myTable:
---------^--here
0
anctechAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GRayLCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anctechAuthor Commented:
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
0
GRayLCommented:
You would use -Sum(patient_refer_insurance)  - spelling reffer?  Thanks, glad to help.
0
GRayLCommented:
Don't like the B though.  Why did you close the question when you still had one?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.