ACCESS FEED: [MS_AccessPros] Re: Counting patients with 3 or more hospitalizations in query

Sabtu, 31 Januari 2015

[MS_AccessPros] Re: Counting patients with 3 or more hospitalizations in query

Ok didn't wait until monday, tried this one, but not working

here is the sql query for this one, I am thinking I am trying to do to many things or something in the sorting is wrong.
basically using a prior query that pulls in the visit types of hosp, urgent and ER, this query limits the types to the er and urgent care, then I want to have it return those with 3 or more in a 6 month period. (tried to get the 6 month period to return on the prior query and having difficulties with those crazy dates- so no worrries right now for that), so using the fields that I have that would result in what data is needed this is the sql for this query
SELECT ER_Hosp_visits_6mos.person_nbr, ER_Hosp_visits_6mos.last_name, ER_Hosp_visits_6mos.first_name, ER_Hosp_visits_6mos.date_of_birth, ER_Hosp_visits_6mos.encounterDate, ER_Hosp_visits_6mos.encounterType, ER_Hosp_visits_6mos.hospitalAdmitDate, ER_Hosp_visits_6mos.sx_procedure2, ER_Hosp_visits_6mos.year_dx, ER_Hosp_visits_6mos.year_px, ER_Hosp_visits_6mos.disease, ER_Hosp_visits_6mos.disease2, ER_Hosp_visits_6mos.create_timestamp, ER_Hosp_visits_6mos.modify_timestamp
FROM ER_Hosp_visits_6mos
WHERE (((ER_Hosp_visits_6mos.encounterType)="Emergency Room")) OR (((ER_Hosp_visits_6mos.encounterType)="Urgent Care"))
ORDER BY ER_Hosp_visits_6mos.encounterType;
WHERE (Person_nbr IN (SELECT Person_nbr FROM ER_Hosp_visits_6mos GROUP BY Person_nbr HAVING Count(*) > 3));

it is returning an error that characters found at the end of the SQL statement and it goes to the WHERE prior to the person nbr info in the second to the bottom line of the query

I am going to now give up until Monday :)
Patty

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

changed the subject but kept the old message to have hx.
yes there is a unique ID for the patient so I will look at doing this. I really appreciate your help. You are a great resource, I will give it a shot on Monday, and let you know how it goes.
thankx
Patty
Director of Quality
WellOne Primary Medical and Dental Care
Rhode Island, USA

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

You should start a new thread for a new problem.

What is the SQL of the other query? What are the fields in the hospital encounters table? If there is a unique ID for PatientID, then you could do something like:

SELECT PatientID

FROM Hospital_Encounters

GROUP BY PatientID

HAVING Count(*) > 3;

If you want patient details, perhaps something like:

SELECT Patients.PatientID, Patients.LastName, Patients.FirstName,

Hospital_Encounters.TreatDate

FROM Patients INNER JOIN Hospital_Encounters

ON Patients.PatientID = Hospital_Encounters.PatientID

WHERE PatientID IN

(SELECT PatientID FROM Hospital_Encounters

GROUP BY PatientID HAVING Count(*) > 3);

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 31, 2015, at 5:28 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

THANK YOU!!!!!
there was a space that caused the error, it now works
YOU ALL ARE WONDERFUL, thank you so much!!

are you up for another challenge?
I have a query where i would like it to tell me who has had 3 hospital encounters,
the table is only hospital encounters, it would have a unique number for each patient, I am guessing that there is a way to show those with 3 or more with some type of formula, just not sure what that would be
Ideas?