Count IF Query

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


On Feb 16, 5:35 am, richardwo
wrote:
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

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

"Jason Lepack" wrote:

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


On Feb 16, 5:35 am, richardwo
wrote:
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

try:

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

Hth

Stu


"richardwo" wrote:

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

"Jason Lepack" wrote:

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


On Feb 16, 5:35 am, richardwo
wrote:
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

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

"stumac" wrote:

try:

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

Hth

Stu


"richardwo" wrote:

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

"Jason Lepack" wrote:

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


On Feb 16, 5:35 am, richardwo
wrote:
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

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

"richardwo" wrote:
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".

"Jason Lepack" wrote:

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


On Feb 16, 5:35 am, richardwo
wrote:
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

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

"richardwo" wrote in message
...
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

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

"John Spencer" wrote:

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

"richardwo" wrote in message
...
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