6 Logical Operators in MS Access that you must know [Operator Types 3 of 5]

While doing any data analysis, you often encounter situations wherein you need to make use of Boolean logic. Now, Boolean logic involves operations on Truth values TRUE (1) and FALSE values (0) .
6 Logical Operators in MS Access that you must know

In simple words, you would be interested, at times, in knowing the output when one expression is true and(/or) second expression is also true. You can think of Boolean logic as a  simple way of comparing individual inputs and expressions. In order to make those comparisons, it uses what are called as operators aka “Logical Operators”.  Note that Boolean logic is a form of logic that reduces all values to either TRUE or FALSE.

In this post, you are going to learn about Logical Operators in MS Access. You will make use of logical operators to combine two expressions and return a value of “TRUE”, “FALSE”, or “NULL” depending on the Boolean value of the expression on which logical operation is being done.

Scenario

You and your manager are busy analysing the company’s sales order data and you want to analyse quite a few scenarios on the data as mentioned below:

  1. From the orders data, you want to extract only those transactions that took place  in New York that were paid  through check as well as all transactions that took place in Illinois and were paid through all except Credit card.
  2. You want  all data from Los Angeles except Cash payments as well as all cash payments transactions except those in Los Angeles. In other words, you want all Los Angeles Cash transactions to be ignored as well as all non-cash transactions from non-Los Angeles city (sic).
  3. As per the  business rule set by the company, once the order is closed, it means that there are no payments pending and that the payments have been done either by cash, check or credit cards. You want a report indicating that this rule is being followed.

Concept: AND, OR, EQV, NOR, XOR, IMP Operations

Before we start creating queries, Let us first understand the concept of logical operators and the kind of operations they perform on the inputs.

In all there are total 6 logical operators in MS Access as follows:

  1. AND OperatorOperation involving AND operator is known as ‘Logical Conjunction’. When you use AND operator between two inputs or operands, the final output will be TRUE (i.e. 1) only if both the inputs/operands are TRUE.
  2. OR Operator Operation involving OR operator is known as ‘ Logical Disjunction’. When you use OR operator between two inputs or operands, the final output will be TRUE (i.e. 1) only if either or both the inputs/operands are TRUE.
  3. XOR Operator– eXclusive OROperation involving XOR operator is known as ‘Exclusive Disjunction’. When you use XOR operator between two inputs or operands, the final output will be TRUE (i.e. 1) if and only if either of the inputs/operands are TRUE but not both. i.e. if both inputs are true or both inputs are false, the output will be false.
  4. Eqv OperatorOperation involving Eqv operator is known as ‘Logical Equality’. As the name suggests, when you use EQv operator between two inputs or operands, the final output will be TRUE (i.e. 1), if and only if both inputs are equal.
  5. NOT OperatorOperation involving NOT  operator is known as ‘Logical Negation’. This operation is performed on only one input or operand. It returns the value of TRUE if the input is FALSE and vice-versa.
  6. IMP OperatorNow, this is a bit tricky one! Atleast I found it so. Nevertheless, pay attention.Operation involving  IMP  operator is known as ‘Logical Implication’.Lets take an example : A IMP B.
    It means that A implies B but B does not necessarily implies A.  In other words, it means if A is true then B has to be true.If B is not true, then the output will be false. It also means if A is false then B may or may not be true. Hence the output will be TRUE in that case.

A

B

A Implies B

0

0

1

0

1

1

1

0

0

1

1

1

Now, the following table shows the output when all the 6 logical operations are performed on two inputs A and B:

A

B

AND

OR

EQV.

XOR

IMP

1

1

1

1

1

0

1

1

0

0

1

0

1

1

0

1

0

1

0

1

0

0

0

0

0

1

0

1

NOT (1) = 0 ; NOT (0) = 1

It is worth noting the outputs of the logical operations if any one or both the inputs are NULL.

A

B

AND

OR

EQV.

XOR

IMP

NOT (A)

0

NULL

0

NULL

NULL

NULL

1

1

1

NULL

NULL

1

NULL

NULL

NULL

0

NULL

0

0

NULL

NULL

NULL

NULL

NULL

NULL

1

NULL

1

NULL

NULL

1

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

Note: In order to understand more about NULL Value, please read the post :What is a NULL Value?

Having learnt the basics of all the 6 logical operators, let us now use these operators to solve the manager’s query mentioned earlier in the scenario:

Using AND, OR, NOT operators

Consider point 1 in the above scenario. It has two parts :

Part 1: You want all the transactions for the sales in New York that were paid in Check.

(City Name = “New York” ANDPayment Type = “Check”)

Part 2: You want all the transactions for the sales in Illinois wherein the payment was made via all payment types except credit cards

(City Name = “Illinois” AND (NOT(Payment Type = “Check”)))

Since you want all the transactions that satisfy either part 1 or part 2, your final criteria statement would look as follows:

Part 1 ORPart 2

Let us see how we can achieve the above result in MS Access:

6 Logical Operators in MS Access that you must know

Based on the screenshot above,

  1. First Select the columns that you want to see in the final output. In this case, since you want all the columns form the table, you specify Orders.* in the query grid. It extracts all the columns for you – no need to select individual columns.
  2. In the second column, specify the criteria and name the column as “Expr”. What it really means is that you are creating a new calculated column with the expression shown above in the screen shot. As you can see,
    • there is an AND operator between Payment Type= “Check” and Ship State/Province =  “NY” . When this part of query is executed, it will return a value of 0 for the transactions that do not satisfy this condition whereas  it will return –1   for those that satisfy this condition.  It should be noted that a new column named “Expr” is being created and across each row in that column a value of 0 or –1 is being calculated. In MS Access, 0 = False and –1 = True.
    • Similarly, there’s  an AND operator between “IL” and NOT (Credit Card). The presence of NOT makes the NOT (credit card)=0, thereby excluding all the transactions with payment type = credit card. Again, based on the calculation, you will get –1 and 0 depending on whether it satisfies the condition.
    • Finally you are adding “OR” operator between the above 2 parts thereby indicating that the transactions satisfying either of the above two conditions, will be returned as TRUE i.e. –1.
  3. Uncheck the checkbox so that, you wont see the column “Expr”. This columns just holds the Boolean values and has no business value to you.
  4. So far, you are getting all the records such that the transactions that satisfy the condition have –1 (TRUE) value across them while others have 0 (FALSE). Since you are interested only in the TRUE values, you’ll specify <> FALSE in the criteria.

Thus, The final output is as follows:

6 Logical Operators in MS Access that you must know

Using XOR (eXclusive OR), Eqv.

On going through the point 2 in the scenario at the beginning, you’ll observe that the result can be achieved using either XOR statement or Eqv statement.

Lets see how,

6 Logical Operators in MS Access that you must know

From the screenshot, you can see that we have selected the columns Order ID, Ship City and Payment Type.

In the next Column, you have XOR operation which would return TRUE (-1), if either the city is Los Angeles or the payment type is cash. It will return FALSE (0) otherwise.

Similarly, in the next column you have a EQV operation. It will return TRUE & FALSE values as discussed in the concept above.

(I have written both the expression side-by-side for you to understand and compare their results. In reality, you’ll actually just use one of them.)

Note – The spelling of ‘Los Angeles’ in the Northwind Database is Los Angelas. This reminds that a wrong spelling in the query will not give any error message. It will simply run the query treating it as FALSE, thereby giving you the wrong output.

On running the query, you get the following output:

6 Logical Operators in MS Access that you must know

But that is not what you are interested in. You want only those transactions that satisfy the condition mentioned in the scenario whereas the above output gives us all the transactions with corresponding Boolean values.

Hence we can make any one of the following two changes.

  • In case you want to use XOR operation , you provide the criteria as TRUE under the XOR column in the query grid.

6 Logical Operators in MS Access that you must know

  • In case you want to use Eqv operation , you provide the criteria as FALSE under the Eqv column in the query grid.

6 Logical Operators in MS Access that you must know

The expected output is as follows:

6 Logical Operators in MS Access that you must know

This shows that the same result can be obtained by any one of the operator but with a slight difference in the way you write the query.

Using IMP Operator

Referring to the point 3 of the scenario, the report can be generated as follows:

6 Logical Operators in MS Access that you must know

As indicated in the screenshot,

  1. First you select the column ‘Status Name’ (from table: Order Status) and column ‘Payment Type’ (from table: Orders). Since you are interested only in the closed orders, specify the criteria “Closed” under the column “Status Name” .
  2. In the last column of the query grid, you’ll provide the expression with IMP operator. This expression will return FALSE ‘0” if the payment type is NULL (i.e pending payment) for a closed order.

On running this query, the output will look as:

6 Logical Operators in MS Access that you must know

Summary

In this post, you have learnt the following logical operators and their impact on inputs such as NULL Values.

  1. AND
  2. OR
  3. XOR
  4. EQV
  5. NOT
  6. IMP

With a bit of logic and creativity, you can use these logical operators to create more complex queries.

Next Steps

Having finished reading this article, you might want to check the following:

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s