Patty-
Close, but no cigar. There needs to be only one WHERE clause, so try this:
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"))
AND (Person_nbr IN
(SELECT Person_nbr
FROM ER_Hosp_visits_6mos
WHERE (((ER_Hosp_visits_6mos.encounterType)="Emergency Room")) OR (((ER_Hosp_visits_6mos.encounterType)="Urgent Care"))
GROUP BY Person_nbr HAVING Count(*) > 3));
ORDER BY ER_Hosp_visits_6mos.encounterType;
Note that I also added the filter for encounterType to the subquery so that you're only counting those types of visits.
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
(Paris, France)
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
(Paris, France)
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?
