Yes, it can be done. You have a problem in that LName plus FName is not good
enough to determine unique patients. Also another problem is multiple
admission periods for the same patient.

Your example shows no break in time frame for an episode. If that is TRUE for
all cases then the following might work for you barring the problem of having
two patients with the same name and matching service dates. That could end up
giving you multiple counts. You would be better off if you had a unique value
for each patient that remained constant across admissions.

SELECT COUNT(adType)
FROM ptRecords as A INNER JOIN ptRecords as B
ON A.LName = B.Lname
AND A.FName = B.FName
AND A.DOS = B.[End Date]
WHERE A.AdType = "Re-Admit"
And B.Place = "NH"
AND A.Place = "Hospital"



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AlwaysLearing wrote:
Hello,
If I have a patient's record as follows:

Lname Fname DOS End Date AdType Place Dicharge
John Smith 4/15/2009 4/27/2009 Initial Admit Hospital
Discharge to Home
John Smith 4/27/2009 5/1/2009 Initial Admit Home Still
at Home
John Smith 5/1/2009 5/3/2009 Re-Admit Hospital Dicharge
to NH
John Smith 5/3/2009 5/20/2009 Initial Admit NH Sill at
NH
John Smith 5/20/2009 5/25/2009 Re-Admit Hospital
Discharge to Home


I need to count how many times Re-Admit shows up in the AdType when
the person came from the NH (the last line). I don't want to count Re-
Admit if the person came to the Hospital from Home (in line 3). I
have about 50,000 lines of almost 20,000 patients. Can it be done?

thanks!