Restricting counts with WHERE clause in report

[Sean Clayton]

Hi,

I need a little bit of assistance with this problem, hopefully before
my brain explodes.

I'm attempting to create a report based on a table of patient records.
The report is intended to show certain date on the patients divided by
month. That part hasn't been a problem; all I did was group the
patients by the relevant date field by putting the date field in a
date header. I also needed to have a count of all the patients in a
particular month; again, no issue, just a count of an ID number in
that same date header.

Here's where things are coming apart, though. I also need to break
that count down by certain yes/no fields. For example, each patient
has a checkbox for ApproachedToConsent, one for Declined, and one for
Consented. I need separate counts for how many patients in a month
ApproachedToConsent, how many Declined, and one for Consented.

What seems clear to me at first glance is to put a WHERE clause in the
Control Source field of a text box, but Access doesn't like that much.
Can someone lend me some ideas?

Thank you in advance,
-Sean

 

[Ken Sheridan]

Sean:

The trick is to Sum the results of an expression which evaluates to 1 or 0
depending on the value of the field in question. So to count patients
approached fro consent create an unbound text box in the footer with the
following as its ControlSource property:

=Sum(IIf([ApproachedToConsent],1,0))

and do similarly for the other Boolean (Yes/No) fields. If the field is a
numeric data type and you wanted to count on the basis of a certain value,
e.g. if there is a field in the report's RecordSource for the patient's age
and you wanted to count all of 50 or over then you'd do this:

=Sum(IIf([PatientAge] >= 50 ,1,0))

if it’s a text field:

=Sum(IIf([Prognosis] = "Gloomy" ,1,0))

Let's hope there's not too many of those!

As you see, in each case the first argument of the IIf function has to
evaluate to True or False, so is in effect the equivalent of your
hypothetical WHERE clause and can be as simple or as complex as you need.

BTW you might see it recommended that you make use of the fact that Boolean
True or False values are implemented in Access as -1 or 0 and use an
expression such as:

=Sum(Abs([ApproachedToConsent]))

or:

=Sum([ApproachedToConsent]*-1)

I'd advise against it. It relies on the implementation which is not good
programming practice.

Ken Sheridan
Stafford, England

 

[Sean Clayton]

Sean:

The trick is to Sum the results of an expression which evaluates to 1 or 0
depending on the value of the field in question. So to count patients
approached fro consent create an unbound text box in the footer with the
following as its ControlSource property:

=Sum(IIf([ApproachedToConsent],1,0))

and do similarly for the other Boolean (Yes/No) fields. If the field is a
numeric data type and you wanted to count on the basis of a certain value,
e.g. if there is a field in the report's RecordSource for the patient's age
and you wanted to count all of 50 or over then you'd do this:

=Sum(IIf([PatientAge] >= 50 ,1,0))

if it’s a text field:

=Sum(IIf([Prognosis] = "Gloomy" ,1,0))

Let's hope there's not too many of those!

As you see, in each case the first argument of the IIf function has to
evaluate to True or False, so is in effect the equivalent of your
hypothetical WHERE clause and can be as simple or as complex as you need.

BTW you might see it recommended that you make use of the fact that Boolean
True or False values are implemented in Access as -1 or 0 and use an
expression such as:

=Sum(Abs([ApproachedToConsent]))

or:

=Sum([ApproachedToConsent]*-1)

I'd advise against it. It relies on the implementation which is not good
programming practice.

Ken Sheridan
Stafford, England

Genius! Worked like a charm. Thank you sir!