ACCESS Not reading SQL conditions

[signature]

I have been unable to manipulate ACCESS to read the SQL statements below. I have been successful in the past. I have been unable to detect why ACCESS does not meet any of the conditions. (Where etc...). The code below gives me the complete contents of both the Market and Trends tables.

What could be interfering with the SQL statements meeting these conditions?

---------------------------------------------------------
select Hospital, Title, FName, LName, Street, City, State, Zip
from Market
where State IN ('NY', 'PA')
and not Hospital = 'USA Hospital'
or not Hospital = 'New Hospital'
or not Hospital = 'Feline Health'
or not Hospital = 'Memorial Hospital'
or not Hospital = 'University Hospital'
or not Hospital = 'Memory Hospital'
or not Title = 'Budget Officer'
UNION select Hospital, Title, FName, LName, Street, City, State, Zip
from Trends
where State in ('NY', 'PA')
and NOT Hospital = 'USA Hospital'
or NOT Hospital = 'New Hospital'
or NOT Hospital = 'Feline Health'
or NOT Hospital = 'Memorial Hospital'
or NOT Hospital = 'Hospital'
or NOT Hospital = 'Memory Hospital'
or NOT Title = 'Budget Officer';
----------------------------------------------------------
Thank you

 

[tomschafer]

There are errors in you Where clause. Try this...

select Hospital, Title, FName, LName, Street, City, State, Zip
from Market
where State IN ('NY', 'PA')
and Hospital NOT IN ('USA Hospital', 'New Hospital', 'Feline Health', 'Memorial Hospital', 'University Hospital', 'Memory Hospital')
or Title <> 'Budget Officer'
UNION select Hospital, Title, FName, LName, Street, City, State, Zip
from Trends
where State in ('NY', 'PA')
and Hospital NOT IN ('USA Hospital', 'New Hospital', 'Feline Health', 'Memorial Hospital', 'Hospital', 'Memory Hospital')
or Title <> 'Budget Officer';

The Where clause should have the expression before the boolean comparison...Hospital NOT =... Which won't work- But Hospital <> will, I believe that Hospital != will also work.
And the IN Clause should speed the query up as well.

Good luck!

 

[tlbroadbent]

The way the query is contructed, Access will choose a record when it mathces one of the states and the hospital is not = 'USA Hospital' OR the hospital is not 'New Hospital' OR the hospital is not... etc.

You need to use AND instead of OR or contruct the query differently.

select Hospital, Title, FName, LName, Street, City, State, Zip
from Market
where (State IN ('NY', 'PA')
and Hospital <> 'USA Hospital'
and Hospital <> 'New Hospital'
and Hospital <> 'Feline Health'
and Hospital <> 'Memorial Hospital'
and Hospital <> 'University Hospital'
and Hospital <> 'Memory Hospital')
' I'm uncertain of your logic so I don't know if the next statement should be AND or OR.
and Title <> 'Budget Officer'
UNION select Hospital, Title, FName, LName, Street, City, State, Zip
from Trends
where (State in ('NY', 'PA')
and Hospital <> 'USA Hospital'
and Hospital <> 'New Hospital'
and Hospital <> 'Feline Health'
and Hospital <> 'Memorial Hospital'
and Hospital <> 'Hospital'
and Hospital <> 'Memory Hospital')
' I'm uncertain of your logic so I don't know if the next statement should be AND or OR.
and Title <> 'Budget Officer';

You could also do the following. I recommend this approach. It is easier to understand.

Select Hospital, Title, FName, LName, Street, City, State, Zip
From Market
Where (State IN ('NY', 'PA')
And Hospital NOT IN ('USA Hospital', 'New Hospital', 'Feline Health', 'Memorial Hospital', 'University Hospital', 'Memory Hospital'))
' I'm uncertain of your logic so I don't know if the next statement should be AND or OR.
And Title Not = 'Budget Officer'
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[signature]

Thank you for the responses and suggestions.

I have been successful with the 2nd suggestion by tlbroadbent without the &quot;=&quot; operand. It seems ACCESS is not reading the &quot;=&quot; or &quot;!=&quot; operand. And the selection for the states using the &quot;IN&quot; reserve word has been only successful when expressed as &quot;where (State in ('NY', 'PA'). (In parens. basically).

It doesn't appear that the &quot;<>&quot; operand is working as well.

I am using ACCESS 2000. Could there be a function in the application that is not set correctly?

I've been following a SQL Handbook and the code in the handbook doesn't seem to be compatible with ACCESS.

Again, Thank you.