Counting CheckBox results

  • Thread starter Thread starter Anna
  • Start date Start date
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.
 
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?
 
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.
 
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.
 
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
 
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.
 
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.
 
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.
 
Back
Top