Solved

How to sum true or yes check boxes in a query?

Posted on 2015-01-31
5
140 Views
Last Modified: 2015-02-09
I have a simple query named: "Flow_Chart_1_Qry" that uses the followin tables
TABLES
1-Incident-tbl
2-Events-tbl
3_Conditions_tbl

Many fields in the query are check box field.

One of the check box fields in the 2-Events-tbl is:
 1-Human Engineering

I want to simply total the number of "TRUE" check boxes for the field:  1-Human Engineering
FlowChartOneTEST-DB.accdb
0
Comment
Question by:cssc1
5 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 167 total points
ID: 40581785
Select Sum(IIF(CheckBox1 = True), 1, 0) AS Checked1 ...
From Table1 ...
Group By ...   if applicable

or just

Select Sum(IIF(CheckBox1), 1, 0) AS Checked1 ...
From Table1 ...
Group By ...   if applicable
0
 

Author Comment

by:cssc1
ID: 40581799
Where do I put this code? In the control source?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 40581807
If you want to just sum the true values then, use this as the ControlSource in the group or report footer.

= Abs(Sum(TheCheckFieldName))

If you want separate sums for true and false, then you need two controls
The first one to count the true

= Sum(IIf(TheCheckFieldName = True, 0, 1))  ''''' this counts both false and null as false.

OR

= Sum(IIf(TheCheckFieldName = False, 1, 0)  '''' this counts only the false values
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 166 total points
ID: 40584511
You can add it as a calculated field in your query.

It's mentioned in my Query Tips and Techniques paper: http://fmsinc.com/MicrosoftAccess/query/index.html#Functions
0
 

Author Closing Comment

by:cssc1
ID: 40599525
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now