Solved

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

Posted on 2008-11-03
7
247 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
[X]
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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime Error '3070' 5 50
MS Access query 16 59
question about a text field with default value 5 30
Access 2003, percentage between two fields 18 13
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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