Count IF Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a query with two fields, Patient Name and FollowUp. FollowUp is a
"Yes/No" field and I would like to show a list of patients with the number of
followUps they had.

This is what I did:

Field: Name FollowUp
Table: tblClinic
Total: Group By Count
Sort:
Show:
Criteria:

The above query returns a list of patients and the total number of followups
they each had, BUT, includes patients who had "No" in the FollowUp field e.g.
John Smith had 3 visits to a clinic but only 2 followups but the query
returns a value of 3 in the FollowUp field.

Is there anyway of calculating the number of actual followups for each
patient i.e. the number of yes's in the followup field.

Thank you in advance. Rich
 
This will show a list of people and their number of yes' but will not
include the people with 0 yes'

Field: Name FollowUp
Table: tblClinic
Total: Group By Count
Sort:
Show:
Criteria: Yes

Cheers,
Jason Lepack
 
Appreciate the help Jason, but this doesn't work. I thought of this myself
but when you run the query it returns exactly the same result ad without the
"yes".
 
try:

Field: nooffollowups: IIF(FollowUp = Yes,1,0)
Table: tblClinic
Total: Group By Count
Sort:
Show:
Criteria:

Hth

Stu
 
Look at SQL View to see what you did.
You filtered on Count being "True"
which will evaluate to True for all positive,
non-zero counts.

SELECT
tblClinic.PatientName,
Count(tblClinic.FollowUp) AS CountOfFollowUp
FROM tblClinic
GROUP BY tblClinic.PatientName
HAVING (((Count(tblClinic.FollowUp))=Yes));

One trick is to sum a boolean field (add all the -1's),
then change sign with Abs function. You can also
use this trick for any boolean expression.

SELECT
tblClinic.PatientName,
Count(*) As TotalCnt,
Abs(Sum(tblClinic.FollowUp)) AS CntFollowUps,
Abs(Sum(tblClinic.FollowUp=0)) AS CntNoFollowUps
FROM tblClinic
GROUP BY tblClinic.PatientName
 
Field: IIF(Followup=True,1,Null)
Total: Count

Count counts the existence of a value. A yes/no field always has a value.

Another way to do this
Field: Abs(Sum(Followup))
Total: Expression

Since True = -1 and False = 0 the above sums everything and gives you a
negative number. To get rid of the negative, apply the abs function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top