Logical Conjunction

The AND Operator

Introduction to Logical Junctions

In the previous lessons, we stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test a condition that depends on another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Suppose you get an assignment to create a database used for a company that rents apartments to customers. The operations would consist of registering customers who become tenants and allocating them an apartment based on their needs and the availability.

1. To create a list of properties that have 2 bedrooms, in the Criteria box of the Bedrooms column, type 2
    
   
2. Switch the query to Datasheet View to see the result
3. Switch it back to Design View
4. Delete the 2 under the Bedrooms column
5. To see a list of available properties, in the Criteria box of the OccupiedVacant column, type "Vacant"
    
   
6. Switch the query to Datasheet View to see the result
7. After viewing the query, switch it back to Design View

For a property rental company, if a customer who is a husband and father of one comes to the rental office and states that he wants a 2-bedroom property, you would check the listing of the properties and find out that you have quite a few of them. To respond to this request, you must examine two conditions for each apartment: The property has two bedrooms The property is available

When preparing your database prior to seeing the customers, you can start by building one query that lists only the properties that have two bedrooms:

The second condition requires that the property be available. From our database, a property is available if its OccupiedVacant field is set to Vacant:

From these two results, notice that there is no relationship between the fact that a property has 2 bedrooms and its being vacant. To rent a property for our customer, it must have two bedrooms. We can create a Boolean truth table as follows:

The property to rent must have two bedrooms. This excludes the 1, 3, and more-bedroom properties. This means that if the property has a number of bedrooms other than 2, whether it is available or not, it cannot be rented to the current customer:

Once we have a list of properties that have two bedrooms, now, let's consider the available properties. If a property has two bedrooms and it is available, then it can be rented to the current customer:

If the property has 1, 3 or more bedrooms but not 2, whether it is available or not, it cannot be rented to the current customer:

In the same way, if both conditions are false (the property has 1 or more than 2 bedrooms and in fact it is not available), the result is false (the property cannot be rented to the current customer):

This demonstrates that a property can be rented to the current customer only if BOTH conditions are met: The property has two bedrooms AND it is available. This type of condition is referred to as logical conjunction.

Logical Conjunction in Tables, Queries, and Forms

As mentioned in previous lessons and sections, before performing data analysis, first display the table or query in Datasheet View, or the form in Form View. After displaying the object, you can use Filter By Form as we saw earlier. To perform logical conjunction, select the values of two (or more) columns in the same row. For example, from our table of videos, imagine that you want to see the videos that were released in 1994 but only those that are rated PG-13. After displaying the Filter By Form window, in the © Year column, you can select 1994 and, in the Rating column, you select PG-13:

After setting the criteria, you can click the Apply Filter button on the toolbar to see the result:

After viewing the result, to restore the table, you can right-click it and click Remove Filter/Sort. This technique of performing logical conjunction on a table is the same used on a query in Datasheet View.

To perform logical conjunction on a form, after applying the filter by form, click each of the desired controls and select the necessary value. For example, the above criteria can be set as follows:

Once again, after setting the criteria, you can right-click the form and click Apply Filter.

Logical Conjunction in SQL Statements

To express the logical conjunction, the SQL uses the AND operator. To use it visually when creating a query, after selecting the columns, in the lower section of the window, click the Criteria box corresponding to each column that will be involved in the conjunction. For example, if you want to create a list of movies released in 1994 but rated R, type the appropriate value in the Criteria boxes of the columns. Here is an example:

To manually create a logical conjunction in SQL, type one condition on the left and the other condition on the right sides of the AND keyword using the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject factors are the same we have used so far. The AND keyword is the new one.

Each condition is written as a SQL operation using the formula:

Column operator Value

In this case, the WHERE operator resembles the If conditional statement. The Condition1 is the first that would be examined. Remember that, from our discussion earlier, if the first condition is false, the whole statement is rendered false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined. Based on this, the SQL statement used to get a list of movies released in 1994 but rated PG-13 is:

SELECT Title, Director, CopyrightYear, Rating
FROM Videos
WHERE CopyrightYear = "1994" AND Rating = "PG-13";

The equivalent SQL statement of the above query in SQL as written by Microsoft Access is:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (((Videos.CopyrightYear)="1994") AND ((Videos.Rating)="PG-13"));

Practical Learning: Applying the AND Operator in a Query

1. To create a list of available properties that have 2 bedrooms, in the Criteria box of the Bedrooms column, type 2 and leave the "Vacant" value for the OccupiedVacant column:
    
   
2. To save the query, on the main menu, click File -> Save As...
3. Type Available 2-Bedroom Properties and press Enter
4. Switch the query to Datasheet View to see the result
    
   
5. Close the query