Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Exclude multiple results from Access query

    I need to exclude multiple results from a medications query. How do I query patients who use MedA, but exclude those patients who use MedA but also use MedB and MedC. Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It depends on how the table(s) is/are organized. Are there several rows for the same patient but for different medications? Are there two (or perhaps three ) tables with relationships of some sort? Is there a single table with one column for each kind of medication? Are the medication IDs stored in a single column of one table?
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    There are 3 tables, Patients, PatientMedications and Medications in a many to many relationship. The PatientMedications table is a junction table with several rows for the same patient but with different medications. The Medications table has 2 fields, medicationID and MedName.

    Thank You

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution among several:
    Code:
    SELECT Patients.PatientName
    FROM Patients
    WHERE (Patients.PatientID IN (
               SELECT PatientMedications.PatientID
               FROM PatientMedications INNER JOIN Medications 
               ON PatientMedications.MedicationID = Medications.medicationID
               WHERE (Medications.MedName="MedA"))
    ) AND (Patients.PatientID Not In (
               SELECT PatientMedications.PatientID
               FROM PatientMedications INNER JOIN Medications 
               ON PatientMedications.MedicationID = Medications.MedicationID
               WHERE (Medications.MedName="MedB") OR (Medications.MedName="MedC"))
    );
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    14
    That works Perfectly! Thank You very much.

    If you have a minute, I would like to take this one step further...
    From an end user standpoint, how would I make this a parameter query where the user would just enter "NOT MedB OR MedC" and get those results?

    Thanks Again

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    As far as I know, parameters queries in Access allow you to enter values for one or more defined parameter(s) when the query is run (open). You're asking for entering values (MedB, MedC) and operators (NOT, OR). You'll probably need to use some VBA for this.
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    14
    I will start learning some VBA. Thank you VERY much for your help.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •