- database solutions and downloads for microsoft access

Microsoft Access SQL

Logical Operators in Access SQL:

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.

  • NOT refers to the logical opposite of your statement.
  • AND joins two items together under a single command.
  • OR makes a command operational if either statement it joins is true.

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.

Combining Logical Operators

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,

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:

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?

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:

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.