Solved

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

Posted on 2008-11-03
7
235 Views
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.
0
Comment
Question by:anctech
  • 5
  • 2
7 Comments
 
LVL 44

Expert Comment

by:GRayL
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:

0
 
LVL 44

Expert Comment

by:GRayL
ID: 22868776
There should be a space:

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

Author Comment

by:anctech
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?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 44

Accepted Solution

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

Author Comment

by:anctech
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
0
 
LVL 44

Expert Comment

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

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

803 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