Count Check boxes and create short report

I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

On Jun 28, 2:24*pm, goodkitty
wrote:
I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

sounds like you might need to write a query that creates a normalized
structure.

This is the basic pattern to do it with the data as you have it...
SELECT DCount("Q1","tblYN","Q1=Yes") AS CountQ1,
DCount("Q2","tblYN","Q2=Yes") AS CountQ2
FROM tblYN;


but a proper design would be

RespondentID, QuestionID, Answer

And then you could Group by QuestionID, and Answer and get a Count of
Answer.

SELECT QuestionID, Answer, Count(Answer)
FROM Response
GROUP BY QuestionID, Answer
ORDER BY QuestionID, Answer;

Hi pietlinden,
Thank You so much for answering. I've been on a crash course of learning
Access 2003 from scratch all by my lonesome with some occasional help from
wonderful people who are willing to help me out. This is one huge database
(over 6,000 records with all sorts of things being tracked) to say the least.

So If I'm reading what you said correctly I need to try a query in the
SELECT ... AS ... FROM .... format. I will try that - is it a particular
"kind" of query - like a union query or simple query or something else?

If I read what you are saying in the design piece - the design of the
database would have to be changed? I'm not sure I could do that, this thing
is sooo complex. The 19 fileds I'm referring to are in the main table called
"Contacts", and the yes/no field names are things like "RiskManagement",
"PatientSafety", "InfectionControl", "HomeCare", "Administration", etc. Each
contact record could have more than one of these selected as yes. Each
Contact has a ContactID in the main table. The fields don't have a
"QuestionID".

Am I reading what you worte correctly? THANK YOU again!

goodkitty

" wrote:

On Jun 28, 2:24 pm, goodkitty
wrote:
I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

sounds like you might need to write a query that creates a normalized
structure.

This is the basic pattern to do it with the data as you have it...
SELECT DCount("Q1","tblYN","Q1=Yes") AS CountQ1,
DCount("Q2","tblYN","Q2=Yes") AS CountQ2
FROM tblYN;


but a proper design would be

RespondentID, QuestionID, Answer

And then you could Group by QuestionID, and Answer and get a Count of
Answer.

SELECT QuestionID, Answer, Count(Answer)
FROM Response
GROUP BY QuestionID, Answer
ORDER BY QuestionID, Answer;

goodkitty wrote:
I have a database that has on one of the tables a selection of 19
checkboxes (for Yes/No Fields). I want to create a report that allows
me to just have a count of how many yes's there are in each field,
that meet a second criteria. I don't want or need the entire list of
checkboxes showing on the report - just a count of how many yes's
there are in field one, how many yes's in field 2, how many yes's in
field 3, etc... through to the last field. I've been searching high
and low for the answer. I have created a query for each of the
different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I
get them to summarize to just the count. I looked into exporting to
excel and then let excel do it - but can't figure that out either.
I'm a novice at Access so need step by step instructions if you know
of a way to do this (including what properties may need changed in a
report field).

I'm not clear. Say you have this:

ID ch1 ch2 ch3 ...
1 Yes No Yes
2. No Yes Yes
3 No No No

Do you want to count the yesses per record?
ID YesCount
1 2
2 2
3 0

Or do you want to count the yesses per checkbox?
ch1 ch2 ch3
1 1 2


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

You can count the instances of TRUE (Yes) values in each column by summing
the return value of an expression which returns 0 or 1. So, you can base the
report on a query such as:

SELECT ContactID,
SUM(IIF(RiskManagement,1,0)) AS CountOfRiskManagement,
SUM(IIF(PatientSafety,1,0)) AS CountOfPatientSafety,
SUM(IIF(InfectionControl,1,0)) AS CountOfInfectionControl,
SUM(IIF(HomeCare,1,0)) AS CountOf PatientSafety,
SUM(IIF(PatientSafety, 1,0)) AS CountOf PatientSafety,
and so on
FROM YourTable
GROUP BY ContactID;

In reality this would probably be joined to a Contacts table which contains
the contact names etc.

Or you can base report on the table (or more likely a query joining tables),
and group the report by ContactID, leave the detail section empty and zero
height, and in the group footer put 19 text boxes, one for each 'factor',
with ControlSource properties using similar expressions to that in the query
above, e.g.

= Sum(IIf(RiskManagement,1,0))

BTW you'll sometimes see it recommended that you use expressions such as
Sum(Abs(RiskManagement)) or Sum(RiskManagement*-1) instead of the above.
Don't do it. It relies on the implementation of Boolean values in Access as
0 (FALSE) or -1 (TRUE). Reliance on the implementation is poor programming
practice; you should always use the Boolean values themselves not their
implementation.

However, as one of the other respondents has pointed out, the table design
is very poor. You are doing what's known as 'encoding data as column
headings' whereas in a relational database data should only be stored as
values at column positions in rows in tables. What you should have is a
Contacts table with a ContactID primary key column and a related table,
ContactFactors say, with a foreign key ContactID column, and a Factor column
which will have values "Risk Management", "Patient Safety", "Infection
Control", "Home Care", "Administration", etc., so for a each 'factor'
relevant to a contact, i.e. where you have a TRUE (yes) value in one of your
current 19 columns, you'd have a row in ContactFactors. For those with a
FALSE (No) value there would be no rows for that contact in ContactFactors.

You should also have a Factors table with 19 rows, one per factor with the
values "Risk Management", "Patient Safety", "Infection Control", "Home Care",
"Administration", etc in a factor column, which will be the primary key of
the table. By relating this table to factors and enforcing referential
integrity you ensure that only valid values can be entered into the factor
column in ContactFactors.

If you create the Factors and ContactFactors tables, then insert the 19
rows into the former you can fill the latter from your current table with 19
'append' queries:

INSERT INTO ContactFactors (ContactID,Factor)
SELECT ContactID, "Risk Management"
FROM YourTable
WHERE RiskManagement;

INSERT INTO ContactFactors (ContactID,Factor)
SELECT ContactID, " Patient Safety"
FROM YourTable
WHERE PatientSafety;

and so on.

I imagine you'll already have a Contacts table or similar with one row per
contact, so your existing badly designed table now becomes redundant, and can
be deleted once you are satisfied that the ContactFactors table (or whatever
you call it) has been correctly populated. But even if not, and you only
have a table with multiple rows per contact, you can fill a new Contacts
table with an 'append' query in which you append only the contact data
(names, address etc), using the SELECT DISTINCT option so only one row is
appended for each.

Ken Sheridan
Stafford, England

"goodkitty" wrote:

I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

Oops! Too much safety. Should have been:

SELECT ContactID,
SUM(IIF(RiskManagement,1,0)) AS CountOfRiskManagement,
SUM(IIF(PatientSafety,1,0)) AS CountOfPatientSafety,
SUM(IIF(InfectionControl,1,0)) AS CountOfInfectionControl,
SUM(IIF(HomeCare,1,0)) AS CountOf PatientSafety,
SUM(IIF(Administration, 1,0)) AS CountOfAdministration,
and so on
FROM YourTable
GROUP BY ContactID;

of course.

Ken Sheridan
Stafford, England

I want to count the Yes's per checkbox.

"Bob Barrows [MVP]" wrote:

goodkitty wrote:
I have a database that has on one of the tables a selection of 19
checkboxes (for Yes/No Fields). I want to create a report that allows
me to just have a count of how many yes's there are in each field,
that meet a second criteria. I don't want or need the entire list of
checkboxes showing on the report - just a count of how many yes's
there are in field one, how many yes's in field 2, how many yes's in
field 3, etc... through to the last field. I've been searching high
and low for the answer. I have created a query for each of the
different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I
get them to summarize to just the count. I looked into exporting to
excel and then let excel do it - but can't figure that out either.
I'm a novice at Access so need step by step instructions if you know
of a way to do this (including what properties may need changed in a
report field).

I'm not clear. Say you have this:

ID ch1 ch2 ch3 ...
1 Yes No Yes
2. No Yes Yes
3 No No No

Do you want to count the yesses per record?
ID YesCount
1 2
2 2
3 0

Or do you want to count the yesses per checkbox?
ch1 ch2 ch3
1 1 2


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thank You. Sounds Like I may need to buckle down and try to do the re-design
and hope I don't mess the whole thing up instead of trying to work around
re-designing this piece of it.

I so appreciate everyone's assistance!

"Ken Sheridan" wrote:

You can count the instances of TRUE (Yes) values in each column by summing
the return value of an expression which returns 0 or 1. So, you can base the
report on a query such as:

SELECT ContactID,
SUM(IIF(RiskManagement,1,0)) AS CountOfRiskManagement,
SUM(IIF(PatientSafety,1,0)) AS CountOfPatientSafety,
SUM(IIF(InfectionControl,1,0)) AS CountOfInfectionControl,
SUM(IIF(HomeCare,1,0)) AS CountOf PatientSafety,
SUM(IIF(PatientSafety, 1,0)) AS CountOf PatientSafety,
and so on
FROM YourTable
GROUP BY ContactID;

In reality this would probably be joined to a Contacts table which contains
the contact names etc.

Or you can base report on the table (or more likely a query joining tables),
and group the report by ContactID, leave the detail section empty and zero
height, and in the group footer put 19 text boxes, one for each 'factor',
with ControlSource properties using similar expressions to that in the query
above, e.g.

= Sum(IIf(RiskManagement,1,0))

BTW you'll sometimes see it recommended that you use expressions such as
Sum(Abs(RiskManagement)) or Sum(RiskManagement*-1) instead of the above.
Don't do it. It relies on the implementation of Boolean values in Access as
0 (FALSE) or -1 (TRUE). Reliance on the implementation is poor programming
practice; you should always use the Boolean values themselves not their
implementation.

However, as one of the other respondents has pointed out, the table design
is very poor. You are doing what's known as 'encoding data as column
headings' whereas in a relational database data should only be stored as
values at column positions in rows in tables. What you should have is a
Contacts table with a ContactID primary key column and a related table,
ContactFactors say, with a foreign key ContactID column, and a Factor column
which will have values "Risk Management", "Patient Safety", "Infection
Control", "Home Care", "Administration", etc., so for a each 'factor'
relevant to a contact, i.e. where you have a TRUE (yes) value in one of your
current 19 columns, you'd have a row in ContactFactors. For those with a
FALSE (No) value there would be no rows for that contact in ContactFactors.

You should also have a Factors table with 19 rows, one per factor with the
values "Risk Management", "Patient Safety", "Infection Control", "Home Care",
"Administration", etc in a factor column, which will be the primary key of
the table. By relating this table to factors and enforcing referential
integrity you ensure that only valid values can be entered into the factor
column in ContactFactors.

If you create the Factors and ContactFactors tables, then insert the 19
rows into the former you can fill the latter from your current table with 19
'append' queries:

INSERT INTO ContactFactors (ContactID,Factor)
SELECT ContactID, "Risk Management"
FROM YourTable
WHERE RiskManagement;

INSERT INTO ContactFactors (ContactID,Factor)
SELECT ContactID, " Patient Safety"
FROM YourTable
WHERE PatientSafety;

and so on.

I imagine you'll already have a Contacts table or similar with one row per
contact, so your existing badly designed table now becomes redundant, and can
be deleted once you are satisfied that the ContactFactors table (or whatever
you call it) has been correctly populated. But even if not, and you only
have a table with multiple rows per contact, you can fill a new Contacts
table with an 'append' query in which you append only the contact data
(names, address etc), using the SELECT DISTINCT option so only one row is
appended for each.

Ken Sheridan
Stafford, England

"goodkitty" wrote:

I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

On Jun 28, 4:57*pm, goodkitty
wrote:
Hi pietlinden,
Thank You so much for answering. I've been on a crash course of learning
Access 2003 from scratch all by my lonesome with some occasional help from
wonderful people who are willing to help me out. This is one huge database
(over 6,000 records with all sorts of things being tracked) to say the least.

So If I'm reading what you said correctly I need to try a query in the
SELECT ... AS ... FROM .... format. I will try that - is it a particular
"kind" of query - like a union query or simple query or something else?

If I read what you are saying in the design piece - the design of the
database would have to be changed? I'm not sure I could do that, this thing
is sooo complex. The 19 fileds I'm referring to are in the main table called
"Contacts", and the yes/no field names are things like "RiskManagement",
"PatientSafety", "InfectionControl", "HomeCare", "Administration", etc. Each
contact record could have more than one of these selected as yes. Each
Contact has a ContactID in the main table. The fields don't have a
"QuestionID".

Am I reading what you worte correctly? THANK YOU again!

goodkitty

" wrote:
On Jun 28, 2:24 pm, goodkitty
wrote:
I have a database that has on one of the tables a selection of 19 checkboxes
(for Yes/No Fields). I want to create a report that allows me to just have a
count of how many yes's there are in each field, that meet a second criteria.
I don't want or need the entire list of checkboxes showing on the report -
just a count of how many yes's there are in field one, how many yes's in
field 2, how many yes's in field 3, etc... through to the last field. I've
been searching high and low for the answer. I have created a query for each
of the different fields to separate out the ones meeting criteria - but that
is 19 queries, and I can't get them into the same report nor can I get them
to summarize to just the count. I looked into exporting to excel and then let
excel do it - but can't figure that out either. I'm a novice at Access so
need step by step instructions if you know of a way to do this (including
what properties may need changed in a report field).

sounds like you might need to write a query that creates a normalized
structure.

This is the basic pattern to do it with the data as you have it...
SELECT DCount("Q1","tblYN","Q1=Yes") AS CountQ1,
DCount("Q2","tblYN","Q2=Yes") AS CountQ2
FROM tblYN;

but a proper design would be

RespondentID, QuestionID, Answer

And then you could Group by QuestionID, and Answer and get a Count of
Answer.

SELECT QuestionID, Answer, Count(Answer)
FROM Response
GROUP BY QuestionID, Answer
ORDER BY QuestionID, Answer;

Essentially what you're describing is something like a Students and
Classes structure.

Student(StudentID (Primary Key), FirstName, LastName...)
Enrollment (or) Grades(StudentID (PK1), CourseID (PK2), Grade)
Course(CourseID, CourseName...)

So if someone has taken a course, he has a record for it in
"enrollment". This is the same idea. So "Risk Management", "Patient
Safety" etc would be records in "Course". The basic idea is to break
up the "repeating field" (implicitly, that's the courses... the only
difference is they have different names, but it's the same idea.) If
you do that, then querying this stuff is easy.

"Enrollment" could just as easily be "Answers' and "Courses" could
correspond to "Questions".

Person---(1,M)---Answers---(M,1)---Questions
Person---(1,M)---Enrolls In---(M,1)---Class

basically, the answers go in the junction table, and everything is
easy to summarize. And you can add Questions and People and Answers
later, and you don't have to change a single thing. Your queries will
still work fine.

Sorry for the really quick crash course, but if you're summarizing a
lot of data, it's easier to build it right than to write union
queries. (I've done that before, and it's a serious hassle!)

Pieter

Well, in that case, your current design will work fine. You would only need
to redesign if you needed to aggregate by ID.
See Ken's replies wor how to do this. No need for me to cover the same
ground.

goodkitty wrote:
I want to count the Yes's per checkbox.

"Bob Barrows [MVP]" wrote:

goodkitty wrote:
I have a database that has on one of the tables a selection of 19
checkboxes (for Yes/No Fields). I want to create a report that
allows me to just have a count of how many yes's there are in each
field, that meet a second criteria. I don't want or need the entire
list of checkboxes showing on the report - just a count of how many
yes's there are in field one, how many yes's in field 2, how many
yes's in field 3, etc... through to the last field. I've been
searching high and low for the answer. I have created a query for
each of the different fields to separate out the ones meeting
criteria - but that is 19 queries, and I can't get them into the
same report nor can I get them to summarize to just the count. I
looked into exporting to excel and then let excel do it - but can't
figure that out either. I'm a novice at Access so need step by step
instructions if you know of a way to do this (including what
properties may need changed in a report field).

I'm not clear. Say you have this:

ID ch1 ch2 ch3 ...
1 Yes No Yes
2. No Yes Yes
3 No No No

Do you want to count the yesses per record?
ID YesCount
1 2
2 2
3 0

Or do you want to count the yesses per checkbox?
ch1 ch2 ch3
1 1 2


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"