If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Count IF Query
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 |
#3
|
|||
|
|||
Count IF Query
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 |
#4
|
|||
|
|||
Count IF Query
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 |
#5
|
|||
|
|||
Count IF Query
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 |
#6
|
|||
|
|||
Count IF Query
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 |
#7
|
|||
|
|||
Count IF Query
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 |
#8
|
|||
|
|||
Count IF Query
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 |
Thread Tools | |
Display Modes | |
|
|