locked
Access 07 Query question on "Where" - need to add a third condition RRS feed

  • Question

  • User1870405786 posted

    I have  sql query from Access 07 that works perfectly.  Now I need to add a 3rd condition to it.  Below is just some of the current WHERE.  How is the best to add the 3rd condition "(((donors.prospect) Is Null))" and make it work?  It should be so simple yet I'm having so much trouble.  I need it to return the people who are NOT employees and NOT donor prospects, yet everythign else listed.

    WHERE (((Medical_Staff.Active)=True)  AND ((HospitalFamily.Employees) Is Null)) OR
    (((HospitalFamily.Employees) Is Null) AND ((Medical_Staff.Courtesy)=True)) OR
    (((HospitalFamily.Employees) Is Null) AND ((Medical_Staff.Honorary)=True)) OR
    (((HospitalFamily.Employees) Is Null) AND ((HospitalFamily.Retirees)=True)) OR ..... there are about 30 more of these.  All have HospitalFamily.Employees Is Null. 

    Any help is GREATLY appreciated! The more I work on it - the more confused I'm getting.

     

    Friday, October 29, 2010 6:34 PM

Answers

  • User-1199946673 posted

    First of all, I think your query could be much shorter and easier to read:

    ((Medical_Staff.Active)=True)

    can be replaced with

    Medical_Staff.Active 

    ,and if you would have

    ((Medical_Staff.Active)=False)

    Replace this with

    NOT Medical_Staff.Active

    Also, in every OR, you have ((HospitalFamily.Employees) Is Null)

    Change your query to:

    WHERE HospitalFamily.Employees Is Null AND (Medical_Staff.Active OR Medical_Staff.Courtesy OR Medical_Staff.Honorary OR HospitalFamily.Retirees OR .....) AND donors.prospect Is Null

    By the way, if HospitalFamily.Employees Is Null, does that mean there's no record in the HospitalFamily table? In that case, HospitalFamily.Retirees doesn't contain a value also, so it makes no sence to include any of the fields in this table in the list of conditions....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 30, 2010 5:24 AM

All replies

  • User-486802730 posted

    not sure I understand your request.....but how about this:

    WHERE (((Medical_Staff.Active)=True)  AND ((HospitalFamily.Employees) Is Null) AND ((donors.prospect) Is Null)) OR
    (((HospitalFamily.Employees) Is Null) AND ((Medical_Staff.Courtesy)=True) AND ((donors.prospect) Is Null)) OR

    etc

    etc 

    Friday, October 29, 2010 7:20 PM
  • User1870405786 posted

    NTC-ASP - thanks for the suggestion.   I tried that before I gave up and came to the experts here.  It didn't pull out the prospects.  That's when my day went downhill.  I know there has to be a way, but for some reason I'm just not getting it figured out.  I'm not sure how to explain it better - sorry.  Your reply was just what I need - it just didn't work.  Any other suggestions? 

    Friday, October 29, 2010 7:55 PM
  • User-1199946673 posted

    First of all, I think your query could be much shorter and easier to read:

    ((Medical_Staff.Active)=True)

    can be replaced with

    Medical_Staff.Active 

    ,and if you would have

    ((Medical_Staff.Active)=False)

    Replace this with

    NOT Medical_Staff.Active

    Also, in every OR, you have ((HospitalFamily.Employees) Is Null)

    Change your query to:

    WHERE HospitalFamily.Employees Is Null AND (Medical_Staff.Active OR Medical_Staff.Courtesy OR Medical_Staff.Honorary OR HospitalFamily.Retirees OR .....) AND donors.prospect Is Null

    By the way, if HospitalFamily.Employees Is Null, does that mean there's no record in the HospitalFamily table? In that case, HospitalFamily.Retirees doesn't contain a value also, so it makes no sence to include any of the fields in this table in the list of conditions....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 30, 2010 5:24 AM
  • User1870405786 posted

    I have lots of records in all the tables under different .names.  And I need some and not others.  And employees can also be any of the other groups as well.  It's a mess.  Employees can be donors, leadership, etc. 

    I used your sample and it pulled them.  YEAH.  Thanks for the guidance.  Happy Dance!!

     

     

    Saturday, October 30, 2010 6:02 PM