Last post Oct 30, 2010 06:02 PM by Sheryl Painter
Oct 29, 2010 06:34 PM|Sheryl Painter|LINK
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.
Oct 29, 2010 07:20 PM|NTC-ASP|LINK
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
Oct 29, 2010 07:55 PM|Sheryl Painter|LINK
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?
Oct 30, 2010 05:24 AM|hans_v|LINK
First of all, I think your query could be much shorter and easier to read:
can be replaced with
,and if you would have
Replace this with
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
Oct 30, 2010 06:02 PM|Sheryl Painter|LINK
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!!