Count Check boxes and create short report

You don't absolutely *need* to restructure the tables, but it would be
advisable. How far you want to go down that road is for you to decide. You
can currently get the results you want with a query or report set up as I
described, but its messy.

Ken Sheridan
Stafford, England

"goodkitty" wrote:

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).