Hi John, thanks! for the solution. I do have the member id as the
unique identifier instead of the last and first name. Also, there
might be a break between the time frame but as long as the discharge
date from the NH to the admit date to the Hospital is within 30 days -
it would than be considered as Readmit. Knowing these two facts, how
would your code be different. Also, is this a macro that I need to
type?

thanks!











On Apr 13, 12:34*pm, John Spencer wrote:
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!- Hide quoted text -

- Show quoted text -