Summing multiple checkboxes

  • Thread starter Thread starter Robbie Doo
  • Start date Start date
R

Robbie Doo

I have a table with a few fields with checkboxes:

ReviewNum CB1 CB2 CB3

0001 Y Y N
0002 N N Y
0003 N Y Y

I would like to create a column in a query where it would list the "Column
Headings" and how many checkboxes are in each column.
Is it possible?
 
Access uses -1 for True (checked) and 0 for False (unchecked.)
You can therefore sum the yes/no fields, and it gives the negative count of
the checked boxes.

In query design, enter an expression like this:
Abs([CB1] + [CB2] + [CB3])

It's clumsy, but that's because this is not the right way to design a
relational database. For more detail, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
 
Thank you Allen:

I will try to change the table.

Allen Browne said:
Access uses -1 for True (checked) and 0 for False (unchecked.)
You can therefore sum the yes/no fields, and it gives the negative count of
the checked boxes.

In query design, enter an expression like this:
Abs([CB1] + [CB2] + [CB3])

It's clumsy, but that's because this is not the right way to design a
relational database. For more detail, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Robbie Doo said:
I have a table with a few fields with checkboxes:

ReviewNum CB1 CB2 CB3

0001 Y Y N
0002 N N Y
0003 N Y Y

I would like to create a column in a query where it would list the "Column
Headings" and how many checkboxes are in each column.
Is it possible?
Click to expand...
Click to expand...
 
Back
Top