Counting CheckBox results

A

Anna

Firstly, my apologies. I know this topic has been discussed a number of
times but I have viewed all the old posts and cannot work out how to
apply this to my database.

I have a table with about 15 yes/no fields. I need to know how to count
the 'yes' answers in each field and group by record (e.g. persons name,
number of 'yes' answers in each field). Each Person can have a number
of separate records - I am trying to find a pattern in the yes/no
responses.

I have created a simple count query, but this will only work for one
field at a time - as soon as I add subsequent fields in the query, all
answers repeat the first field count.

I am really only familiar with using the query design view, so please
'spell it out for me' if it requires using sql.
 
D

Douglas J. Steele

Don't use Count, use Sum.

Boolean values are stored as -1 for True and 0 for False. If you sum the
field and get, say, -11, you'll know that 11 of them are True.

However, are you sure that your tables have been properly normalized? 15
Yes/No fields sounds a little suspicious to me: are you sure that shouldn't
be 15 rows in a second table instead?
 
A

Anna

Thanks this is working now, is there a way of getting it to display as
a positive number for the reports I set up?

As far as my table goes - I may be explaining it incorrectly or you
could very well be right. I have a table for personal details and then
a table named 'incident'. This table contains 21 fields which are set
as a yes/no datatype. I did this because more than one field can be
checked for any person. There are also a number of other fields which
have been set up with a dropdown menu using the look-up wizard. I'm not
sure that I understand what 'normalised' means.
 
D

Duane Hookom

PMFJI,
You can use the Abs() function to change negative values into positive
values.

Your structure isn't normalized. Jeff Conrad (MS MVP) has some resources at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.
You might want to take a look at two files at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The At
Your Survey and Employee Evaluation samples are both fairly normalized. YOu
would be able to add incident types without changing the table structure or
any forms or reports.
 
A

Anna

I'm sorry, I now understand what you mean by normalisation but cannot
see where I need to change. I assume I need to make a separate table
for 'type of behaviour' (but wouldn't this still have 21 yes/no
fields?), a table for location (currently dropdown list .. where did
behaviour take place?) and a table for time (currently dropdown list ..
what time did behaviour take place?). There would always be a response
for all three of these tables per person .. Location and Time have only
one selection per person. Behaviour is multiple selections
 
D

Duane Hookom

You would create a junction table where each record would contain an
IncidentID and a type of behavior value. If you had 10 boxes checked in your
previous design, this would create 10 individual records in your normalized
tables.
 
F

Fred Peters

What if I have 40 fields of yes/no and only want a form to show the yes'.
Basically, it having to do with awards. If you have this award check the
box. I don't want to show the fields that are no. Every person has a
different amount of yes'. ie. This person has 3 checked yes', this person
has 10 check yes'. When I run the form, it only shows the first person with
the 3 checks, next record would show the 10 checked yes'. Need to keep the
no's invisible.
 
D

Duane Hookom

Do you understand that your table is not properly normalized? What happens
if you need to add or subtract an award? I would think that with your
design, you would need to add and subtract fields, controls, columns,...

Each award given should create a new record in a related table. This would
make your question un-necessary since the table would not contain any "no"
values.
 
Top