Queries using "not"

  • Thread starter Thread starter Miaplacidus
  • Start date Start date
M

Miaplacidus

I'm trying to write a query that lists all employees
except Glenn in Accounting, so I have a query that lists
all the employees and their department, then in criteria
under name I have <>"Glenn" and under Department I have
<> "Accounting".

This produces a table listing everybody outside of
accounting. Shouldn't this exclude only Glenn from
Accounting?
 
Not quite - this is just a problem with the logic. Move the second condition
down a line in the query grid, this will cause the SQL to use the 'OR'
operator between the two conditions since you want all records where
Name<>"Glen" or Dept<>"Accounting". The only records that will be *excluded*
are the ones in which both Name="Glenn" and Dept="Accounting".

Using simple conditions represented by A and B, you had:

(Not A) AND (Not B)

which is logically equivalent to:

Not (A or B)

which is why all people in Accounting were excluded. If you had other Glenns
outside of Accounting they were probably excluded too.

What you needed was

Not (A and B)

which is harder to write using the query grid but is logically equivalent
to:

(Not A) OR (Not B)

Thus ending a brief lesson in Boolean algebra. :-)
 
Back
Top