The sql queries below need to be combined so that it further reduces the results. One of these will need to be a subquery. I am newbie with Access and am only getting errors. The end result should further filter the results to only show the encounters meeting all criteria in both of the querys. Both of these result in the correct result individually...any help you could provide would be greatly appreciated.
SELECT encounters.encounter_id, medications.encounter_id,
medications.medication_id, medication_types.medication_id,
medication_types.name, medication_types.class
FROM medication_types
INNER JOIN (encounters
INNER JOIN medications ON encounters.encounter_id = medications.encounter_id)
ON medication_types.medication_id = medications.medication_id
WHERE medication_types.class LIKE '*Antibiotic*';
SELECT encounters.encounter_id, encounters.admit_year,
diseases.encounter_id, diseases.disease_id,
disease_types.disease_id, disease_types.icd9cm
FROM encounters
INNER JOIN (disease_types
INNER JOIN diseases ON disease_types.disease_id = diseases.disease_id)
ON encounters.encounter_iD = diseases.encounter_id
WHERE disease_types.icd9cm IN ('041.3','480.0','480.1','480.2','480.3','480.8','480.9','481','482.1','482.2','482.9','486','V03.82','V12.61')
AND admit_week BETWEEN 5 and 9
AND encounters.admit_year = 2014
ORDER BY encounters.admit_week;
If you don't need to display the medications and diseases, just return the encounter info, consider:
SELECT DISTINCT encounters.encounter_id, admit_year FROM Query2 WHERE encounters.encounter_id IN (SELECT encounters.encounter_id FROM Query1);