Summing multiple checkboxes

[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?

 

[Allen Browne]

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

 

[Robbie Doo]

Thank you Allen:

I will try to change the table.

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.

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?