Count IF Query

[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

 

[Jason Lepack]

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

 

[Guest]

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

 

[Guest]

try:

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

Hth

Stu

 

[Guest]

Oops, change count to sum. Count will count all records regardless of its
value!

 

[Gary Walter]

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

 

[John Spencer]

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

 

[Guest]

Thank you all for your help. Got is sorted now thanks to your help John.