Exclusion Logic

[kcmtnbiker]

What is the WHERE syntax to exlude records meeting criteria accross two
fields. The best way to explain what I am trying to accomplish to provide a
simple example

STATE RATE
AL 1.5
AL 2
MO 1
MO 2
AL 1

I want to exlude all records where state = "AL" and Rate <=1.5. Records not
meeting this criteria should be returned as part of my query.

 

[kcmtnbiker]

After running the query, I would expect the results to be:
STATE RATE
AL 2
MO 1
MO 2

 

[kcmtnbiker]

I think I have answered my own question. Looks like this should actually be
an "OR" operation as opposed to an "AND" operation. This WHERE function
returns the desired results:

WHERE ([rate]>"1.5") OR ([STATE]=AL)

 

[KARL DEWEY]

Try this --
SELECT STATE, RATE
FROM YourTable
WHERE Not (STATE = "AL" AND RATE<=2);

 

[John Vinson]

What is the WHERE syntax to exlude records meeting criteria accross two
fields. The best way to explain what I am trying to accomplish to provide a
simple example

STATE RATE
AL 1.5
AL 2
MO 1
MO 2
AL 1

I want to exlude all records where state = "AL" and Rate <=1.5. Records not
meeting this criteria should be returned as part of my query.

SELECT <whatever>
FROM tablename
WHERE NOT (State = "AL" AND [Rate] <= 1.5);

or, equivalently,

SELECT <whatever>
FROM tablename
WHERE [State] <> "AL"
OR Rate > 1.5;

John W. Vinson[MVP]

 

[kcmtnbiker]

That's it. Thanks

Try this --
SELECT STATE, RATE
FROM YourTable
WHERE Not (STATE = "AL" AND RATE<=2);