Counting Check boxes in a query

  • Thread starter Thread starter Cheryl T.
  • Start date Start date
C

Cheryl T.

I used check boxes in a database for vehicle accidents to
identify driver fault. I want to count the number of
boxes that are checked to return a total in a report. I'm
assuming I need to do this in my query. However, when I
use the "count" function in the total line of the query,
it returns the count of all the boxes, whether or not they
are checked. Please advise. Thanks, Cheryl T
 
Cheryl,

I assume that you mean that you used a Yes/No data type. It's hard to
tell exactly what it is you want to do with this count of the number
of checked boxes, but I would probably use an aggregate query, change
the value in the column to ABS([fieldname)], and in the Totals row,
select Sum. Since a Yes = -1 and a No = 0, summing the absolute value
of these numbers will give you the number of records where the driver
fault field is checked.

--
HTH

Dale Fye


I used check boxes in a database for vehicle accidents to
identify driver fault. I want to count the number of
boxes that are checked to return a total in a report. I'm
assuming I need to do this in my query. However, when I
use the "count" function in the total line of the query,
it returns the count of all the boxes, whether or not they
are checked. Please advise. Thanks, Cheryl T
 
I must be missing something, because it's still counting
every box, even the blank ones.
 
Ok. I did what you said, and I do get the correct count
in the query, but for some reason I can't get a sum to
work in the report. I'm using the syntax = Sum
([fieldname]). Any idea what I'm doing wrong there?
Thanks,
 
Not in the Query since I assume that you want to have all the Records for
the Report.

However, you can use the Report to do the sum as per Dale's advice. In the
Report Footer section, add a TextBox Control and set its ControlSource to:

= ABS(Sum([DriverAtFault]))

where [DriverAtFault] is the appropriate Field name in your RecordSource
Query for the Report.
 
I have added an additional column in the query to collect
the values (1 or 0), so it doesn't delete any records to
have the calculation there. In fact, it seems to do
exactly what Dale said it would. I'm just getting an
error message when I try to do a total field in the
report. I will try what you suggested.
-----Original Message-----
Not in the Query since I assume that you want to have all the Records for
the Report.

However, you can use the Report to do the sum as per Dale's advice. In the
Report Footer section, add a TextBox Control and set its ControlSource to:

= ABS(Sum([DriverAtFault]))

where [DriverAtFault] is the appropriate Field name in your RecordSource
Query for the Report.

--
HTH
Van T. Dinh
MVP (Access)



Cheryl T. said:
I must be missing something, because it's still counting
every box, even the blank ones.
Click to expand...


.
Click to expand...
 
Just tried your suggestion - still getting an error
message in the report.
-----Original Message-----
Not in the Query since I assume that you want to have all the Records for
the Report.

However, you can use the Report to do the sum as per Dale's advice. In the
Report Footer section, add a TextBox Control and set its ControlSource to:

= ABS(Sum([DriverAtFault]))

where [DriverAtFault] is the appropriate Field name in your RecordSource
Query for the Report.

--
HTH
Van T. Dinh
MVP (Access)



Cheryl T. said:
I must be missing something, because it's still counting
every box, even the blank ones.
Click to expand...


.
Click to expand...
 
Cheryl said:
Just tried your suggestion - still getting an error
message in the report.
-----Original Message-----
Not in the Query since I assume that you want to have all the Records for
the Report.

However, you can use the Report to do the sum as per Dale's advice. In the
Report Footer section, add a TextBox Control and set its ControlSource to:

= ABS(Sum([DriverAtFault]))

where [DriverAtFault] is the appropriate Field name in your RecordSource
Query for the Report.

--
HTH
Van T. Dinh
MVP (Access)



Cheryl T. said:
I must be missing something, because it's still counting
every box, even the blank ones.
Click to expand...
Click to expand...
Click to expand...

Cheryl,
Van's expression..
= ABS(Sum([DriverAtFault])
will return the correct count of all records with a Yes check.
A Yes Check field has a value of -1.
A No Check field has a value of 0.
If you Sum all the records you will get the negative count of the
records marked Yes. The ABS() function returns a positive value.

If you are getting an #Error it is probably because you did not start
out with an unbound control, but rather used the original
[DriverAtFault] control and simply changed the control source to the
above expression.
An Access control can NOT have the same name as a field used in it's
control source expression. Change the control name to something else.
 
Back
Top