exclude records upon criteria

[andrea_celedon]

Hi! New to the forum and immensely happy that I have found you

I'm needing some expert help to filter out records on a query built on Access 2003. Will try to be as specific as possible, but feel free to ask if you need more details. Here it goes.

I work with patient data. Each person can have more than one type of diagnosis, which comes as a new record. Now, the criteria used to extract data is:

- All patients with diagnosis code I200 or I211 or I214.
- But if they have diagnosis code I500 as well then all records for that patient needs to be filtered out.

ie: View attachment patrecords.xls I've highligted every different person.

Because every diagnosis comes in a new row, I can not do the basic query criteria of "I200" or "I211" or "I214" and not "I500". Instead, I need a formula that looks for matching PATIENT NUMBER and then searches among DIAGNOSIS CODES to pull just the right patients. Does that makes sense?

I'm happy to have a VBA formula that bring a Yes/No (Keep/Exclude) status so then I can filter easily. Or there might be an SQL solution... dunno. You tell me what's best

Thanks heaps!

 

[John Big Booty]

One way you could do this is to first create yourself a query that selects all the patients that do have a diagnosis of I500. Now use the "Find Unmatched Query Wizard" to build a query based on that query and you table to return patients who have a diagnosis of I200 or I211 or I214 but not I500.

The SQL would look something like;

SELECT TablePatient.PatientID, TablePatient.Diagnosis
FROM TablePatient LEFT JOIN QueryDiagnosisI500 ON TablePatient.PatientId = QueryDiagnosisI500.PatientID
WHERE (((QueryDiagnosisI500.PatientID) Is Null));

 

[John Big Booty]

This link may be of assistance.