Originally, logical operators were a concept from algebra used to exclude and include number sets. They are, very simply, NOT, AND, and OR. Sometimes this set is expanded to combinations of the operators, but at core it's only these three. The logical operators are often referred to as the Boolean operators.
When conducting an SQL task in Microsoft Access, the logical operators can be used almost anywhere. You can combine them with DELETE, and WHERE:
DELETE name AND date WHERE employee.title = cashier AND employee.yearsservice = >2
The above operation would delete the fields "name" and "date" for all cashiers with less than two years of service. Simple, right?
But then it starts to get complicated. As many as 252 statements in a query string can be joined with the logical operators, so you'll probably always have room for more. But the more you have, the more complex your string becomes, and the easier it is to break it.
What if you needed to run a query where you wanted only employees with the title "janitor" or department "maintenance" to be available? You would run a query:
SELECT * FROM employees WHERE employee.department = maintenance OR employee.title = janitor
This operation, once run, selects all fields of all records from employee that are marked either maintenance department or janitor.
If the janitor was part of the maintenance department and you wanted to exclude him, your search would be:
SELECT * FROM employees WHERE employee.department = maintenance OR NOT employee.title = janitor
That looks funny, but syntactically, it's perfect.
In your query string, the logical operators are used in the order NOT AND OR. That means in any line, the NOT statements are performed first, then the AND, then the OR.
If you're not paying attention, this can mess you up. What if you're running a string in which you want (these two statements) or (these two statements). For instance,
SELECT * FROM pets WHERE species = dog AND type = brown OR species = cat AND size = small
If you allow this query string to run just as it's written, you wind up with a table that includes only brown dogs and small cats. But you can change the order of operation by using parenthesis:
SELECT * FROM pets WHERE species = dog AND (type = brown OR species = cat) AND size = small
This returns a data set with small brown dogs – since it's not likely you're going to have a dog that is also a cat, that part of your query string is basically junk. But the search, with parenthesis to change the order of operation, returns very different results from the earlier search.
Now to add one more item to the list. Mathematical operators (*+-/ and all the rest) are performed before any logical operator. So what if you were looking by price as well?
SELECT * FROM pets WHERE species = dog OR species = cat AND price = >200 AND size = small
This search string returns all dogs or cats that are small and less than $200 in cost. If you messed around with parenthesis in this one:
SELECT * FROM pets
WHERE species = dog OR (species = cat AND price = >200 AND size = small)
This would return all dogs, and also all small cats costing less than $200.
When working with logical operators in SQL and Microsoft Access, pay attention to your order, and you'll do just fine.