How can I do this in MSS-Access sql?
I have 2 table with a common key. tblPatient and tblMeds linked by chart. Is there a way in sql to get all the patients that have (for example) MedicationA, MedicationB and MedicationC? I want all the patients that take all 3 medications. Sounds simple but it dosn't seem to be. Thanks in advance.
Jeffrey Coachman
Then why not post what you tried, so we can look at it...?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have tblPatients and tblMeds. You need a third table tblPatsMeds containing the Meds taken by each patient. In this case, you want to know which patients in tblPatsMeds has a record for each of meds MedA, MedB, and MedC.
TRANSFORM First(PatID)
SELECT PatID
FROM tblPatsMeds
GROUP BY PatID
PIVOT MedID IN ("MedA","MedB","MedC")
Once you see that this works, you can get additional details like FName, LName, MedName by Joining to tblPatients and tblMeds.
TRANSFORM First(PatID)
SELECT PatID
FROM tblPatsMeds
GROUP BY PatID
PIVOT MedID IN ("MedA","MedB","MedC")
Once you see that this works, you can get additional details like FName, LName, MedName by Joining to tblPatients and tblMeds.
ASKER
Thanks, that does it!