Conjunction and Disjunction in Data Analysis

Logical Disjunction

Introduction

Consider a database that contains a list of students with various pieces of information. Imagine that our main goal is to make sure that we have a number we can use to contact somebody for the student. In this case, either the home phone number or the emergency number would be fine. We already have the following two statements:

1. This student's record indicates a home phone number
2. This student's record provides an emergency phone number

To get either number, we can concatenate these two statements and evaluate the truthfulness of either one of them. We create a new statement as follows:

• "This student's record indicates a home phone number" OR "This student's record provides an emergency phone number" 
  The comparative evaluation produces the following results:

• If the student's record indicates a home phone number, our goal is met and we do not need to evaluate the second statement. Therefore, the combined statement is true.
• If the student's record does not indicate a home phone number, we can then consider the second statement.
  If the student's record provides an emergency phone number, we have a number we can use. This means that the result of the first statement is not important. Therefore, the combined statement is true.
• If the student's record indicates a home phone number and the student's record provides an emergency phone number, our goal is still met; we can use either number. Therefore, the combined statement is true.
• If the student's record does not indicate a home phone number, then we consider the second statement. If the student's record does not provide an emergency phone number, our goal is not met: we do not have any number to contact somebody for the student in case of emergency. Therefore, the combined statement is false.

The logical disjunction shows us that a combined statement is true if EITHER of its two statements IS TRUE. The logical disjunction produces a false result if BOTH of its two statements are FALSE.

To perform a logical disjunction, you can use the OR operator.

Practical Learning: Introducing Logical Disjunction

1. From the resources that accompany our lessons, open the Altair Realtors2 database
2. On the Ribbon, click Create and, in the Other section, click Query Design
3. In the list of tables, click Properties1, click Add, and click Close
4. From the list of fields, double-click Property #, Property Type, City, State, Bedrooms, Bathrooms, Condition, and Market Value

Using a Logical Conjunction

To perform filtering by logical disjunction, you can use filter by value, Filter By Form, or use a grid. Consider the following table of videos from a database named Video Collection:

To perform filtering by selecting this or that value:

• Click a column header or any cell under a column for a table or query, or click a control or its accompanying label on a form. Then, in the Sort & Filter section of the Ribbon, click Filter
• On a table or query, click the down-pointing button on the right side of the name of the column

A window would come up with the values from the column you selected and each value would have a check box. To apply a logical disjunction, put the check mark on at least two values excluding the (Select All) option. Here is an example:

After making the selections, to apply the filter click OK. This would result in the records that use either of the selected values:

To remove the filter, in Lesson 17, we saw that you could click the Toggle Filters button on the Ribbon or the Filtered button on the table, query, or form. As an alternative, you can display the window again, click the (Select All) option and click OK.

To filter by form, open the table, query, or form. in the Sort & Filter section of the Ribbon, click Adv anced -> Filter By Form. Click the combo box under the first desired field and select the desired value. Then click the combo box under the other desired field and select the desired value:

In the bottom section of the window, click the Or tab. Then, on the window, click the combo box under the same column and select another value. Here is an example:

After selecting, apply the filter. The resulting list would include the records that use either of the selected values:

As an alternative, you can select a value from one column for the first value, click the OR tab, then select a value from another column.

To filter using a grid, open the table or form. in the Sort & Filter section of the Ribbon, click Advanced -> Advanced Filter/Sort. Select the field and put it in the bottom section of the window. In the Criteria box that corresponds to the column, type the desired value or expression. In the OR box that corresponds to the same column, enter another value. Here is an example:

After specifying the values, apply the filter. The resulting list would include the records that use one or the other value.

In the same way, to use a logical disjunction on a query, open it in Design View and select all the fields that will be part of the query. In the Criteria box that corresponds to the desired columns type the desired expression and press the down arrow key. In the OR box of the same column, or the OR box of another column, type the desired value or expression. Then display the query in Datasheet View.

All of the types of values or logical operators we reviewed in the previous lesson can be applied to a logical disjunction:

• If the value is a number, just type it
• If the value is a string, include it in double-quotes
• If the value is a date or time, include it between # signs
• If the value is Boolean, type it as TRUE or as FALSE

Remember that everything is case-insensitive.

As stated already, the SQL supports the logical disjunction with an operator called OR. Besides the SELECT statement, the formula to use is:

WHERE Expression1 OR Expression2

The WHERE and the OR keywords are required. Each expression is in the form:

FieldName Operator Value

This means that you must specify a column, the operator that performs the filtering, and the value applied to the column. Here is an example:

SELECT Title, Director, CopyrightYear, Rating, Length, Format, [Wide Screen]
FROM   Videos
WHERE (Rating = "Unrated") OR (Rating = "R");

Practical Learning: Using Logical Disjunction

1. In the bottom section of the table, under State, click the corresponding Criteria box and type VA
2. In the or box of the same State column, type MD and press Enter
    
   
3. Right-click the title bar of the window and click SQL View to see the SQL code
    

   SELECT 	Properties1.[Property #],
   	Properties1.[Property Type], 
   	Properties1.City, 
   	Properties1.State, 
   	Properties1.Bedrooms, 
   	Properties1.Bathrooms, 
   	Properties1.Condition, 
   	Properties1.[Market Value]
   FROM 	Properties1
   WHERE 	(((Properties1.State)="VA")) OR (((Properties1.State)="MD"));

4. Right-click the title bar of the window and click Datasheet View
5. Right-click the title bar of the window and click Design View
6. In the bottom section of the window, click the check box of State (because we know where each city is, there is no need to show the name of the state)
7. Right-click the title bar of the window and click Datasheet View
8. Right-click State and click Sort A To Z
    
   
9. Close the query
10. When asked whether to save it, click Yes
11. Set the name to Maryland and Virginia Listing and click OK
12. Create a tabular form for the Maryland and Virginia Listing query and save it as Maryland and Virginia Listing
13. Create a tabular report for the Maryland and Virginia Listing query and save it as Maryland and Virginia Listing

Logical Conjunction

Introduction

Imagine you are reviewing the students’ records of a school and you want to make sure that you know whom to contact in case of emergency for each student. For example, imagine that you are trying to make sure the record of each student provides an emergency telephone number. The statement to evaluate would be:

• This student's record provides an emergency phone number

If the record of the student referred to contains an emergency phone number, the statement is true. If the record does not provide this information, our goal is not met and the statement is false. Imagine that, on the other hand, we want to find out if the record of each student displays the name to contact in case of emergency. The statement considered would be:

• This student's record provides an emergency name

If the record considered displays an emergency name, the statement is true.

Imagine that a student's record indicates an emergency telephone number but does not provide an emergency name. What if a student's record indicates an emergency name but no emergency telephone number? It appears that in the absence of one of these pieces of information, it would be hard to perform an effective contact. To make a record complete or to accomplish our goal, we want each record to have both an emergency name and an emergency telephone number. We can combine both of the above statements as follows:

• "This student's record provides an emergency phone number" AND "This student's record provides an emergency name"

Evaluating the combined statement would produce the following results:

• If the record of the student referred to does not provide an emergency phone number, the record of the student is not complete and our goal is not met, regardless of the second statement. Therefore, the combined statement is false.
• If the record of the student referred to provides an emergency phone number, then we would consider the second statement.
  If the record does not provide a name in case of emergency, the record is not complete and our goal is not met. Therefore, the combined statement is false.
• If the record of the student being considered provides neither an emergency phone number nor an emergency name, the record is still not complete and our goal is not met. Therefore, the combined statement is false.
• If the record of the student being considered provides both an emergency telephone number and an emergency name, we consider that the record is complete and our goal is met. Therefore, the combined statement is true.

To resume, our goal is met only if BOTH the first and the second statement are TRUE.

On a table or query that displays in Datasheet View, or a form, to combine two statements, you can use Filter by Form and select a criterion for each field that is being considered. Another alternative is to concatenate (join) statements. This is done using the AND operator.

Practical Learning: Introducing Logical Conjunction

1. If necessary, on the Ribbon, click Create and, in the Other section, click Query Design.
   In the list of tables, click Properties1, click Add, and click Close
2. From the list of fields, double-click Property #, Property Type, City, Bedrooms, Bathrooms, Year Built, Condition, and Market Value

Using a Logical Conjunction

To filter by value, open the table, query, or form:

• Click a column header or any cell under a column for a table or query, or click a control or its accompanying label on a form. Then, in the Sort & Filter section of the Ribbon, click Filter
• On a table or query, click the down-pointing button on the right side of the name of the column

A window would display the values from the column you selected and each value would have a check box. Put a check mark on one value excluding the (Select All) option and leave the others uncheck. Here is an example:

After checking the value of the first field, click OK. Access another field and select one of its values. Here is an example:

After clicking OK, the list would include only the records that use both values:

It is important to know that the order of selecting the fields is not important. In this example, we could have started with the Director field and we would get the same result.

To filter by form, open the table, query, or form. in the Sort & Filter section of the Ribbon, click Advanced -> Filter By Form. Click the combo box under the first desired field and select the desired value. Then click the combo box under the other desired field and select the desired value:

Once again, the order in which you select the values is not important. What is important for a logical conjunction is that you select one value for each of two fields. After selecting, apply the filter. The resulting list would include only the records that use both values:

To filter using a grid, open the table or form. in the Sort & Filter section of the Ribbon, click Adv anced -> Advanced Filter/Sort. Select the first field and put it in the bottom section of the window. In the Criteria box of the column, type the desired value or expression. Add another column to the bottom section of the window. In its Criteria box, type the desired value:

After specifying the values, apply the filter. The resulting list would include only the records that use both values:

In the same way, to use a logical conjunction on a query, open it in Design View and select all the fields that will be part of the query. In the Criteria box corresponding to one of the columns, type the desired expression. In the Criteria box of another column, type the desired value or expression. Then display the query in Datasheet View.

All of the types of values or logical operators we reviewed in the previous lesson can be applied to a logical conjunction.

The SQL supports the logical conjunction using the AND operator. Besides the SELECT statement, the formula to use is:

WHERE Expression1 AND Expression2

The WHERE and the AND keywords are required. Each expression is in the form:

FieldName Operator Value

You must specify a column, an operator, and a value.

Practical Learning: Using Logical Conjunction

1. In the bottom section of the table, under City, click the corresponding Criteria box and type "Silver Spring"
2. Again in the bottom section of the table, under Market Value, click the corresponding Criteria box and type <= 500000
    
   
3. Right-click the title bar of the window and click SQL View to see the SQL code
   

   SELECT 	Properties1.[Property #], 
   	Properties1.[Property Type], 
   	Properties1.City, Properties1.Bedrooms, 
   	Properties1.Bathrooms, 
   	Properties1.[Year Built], 
   	Properties1.Condition, 
   	Properties1.[Market Value]
   FROM 	Properties1
   WHERE 	(((Properties1.City)="Silver Spring") AND 
   	 ((Properties1.[Market Value])<=500000));

4. Right-click the title bar of the window and click Datasheet View
5. Close the query
6. When asked whether to save it, click No

Combination of Logical Operators

You can apply more restrictive filters by combining logical operators, as long as you follow some rules. Imagine that in your database of students, you want to get a list of girls whose records indicate either a home telephone number or an emergency telephone number. To find these records, you should proceed by logic thinking and combine filters that can produce the right result. Because databases are varied and scenarios are different, there is no strict recipe to follow, just some rules and suggestions.

Imagine that, in your database of students, you want to see a list of girls who live in a Single Parent home. Using the Filter by Form on a table, in the Gender column, you can just select F and, in the Single Parent? column, you would select the check box.

Imagine now that you want a list of male students whose records display either a home telephone number or an emergency telephone number. The expression to evaluate would look like this: "Home Phone Contains Something For Each M Gender" AND "Emergency Number Contains Something For Each M Gender". To create the criteria using Filter by Form on a form, for the first criterion, you would select M in the Gender combo box and, in the Home Phone field, you can type IS NOT NULL (which means that this field must not be empty for the criterion to apply):

Then click the OR tab and proceed the same for both the Gender combo box and the emergency telephone number field:

After applying the filter, you would notice that the list displays only boys whose records have either of both telephone numbers.

Suppose you want a list of girls whose records have incomplete information on who to contact in case of emergency. To satisfy this goal, we want each girl to have either a home telephone number OR both an emergency name AND an emergency telephone number. We believe that, if the record has a home number, we can call her home and ask for Mr. or Mrs. Last Name. If the record does not have that number, we want to have an emergency number. Because we saw earlier (or as our records of some students indicate) that an emergency number is not necessarily the (direct) parent, we want to make sure that we have a corresponding emergency name. Such an expression can be created as follows:

"<'Gender=F' AND 'Home Phone IS NULL'> OR <'Gender=F' AND 'Emergency Name IS NULL AND Emergency Phone IS NULL'>".

Using the Filter By Form on a form, for the Look For section, we would select the F Gender and, in the Home Phone field, we can type IS NULL. Then, we click the OR tab and, in the Gender combo box we select F; in the Emergency Name, we would type IS NULL and, in the Emergency Phone, we type IS NULL:

Practical Learning: Combining Logical operators

1. If necessary, on the Ribbon, click Create and, in the Other section, click Query Design.
   In the list of tables, click Properties1, click Add, and click Close
2. From the list of fields, double-click Property #, Property Type, City, State, ZIP Code, Bedrooms, Bathrooms, Stories, Year Built, Condition, and Market Value
3. Right-click the title bar and click SQL View to see the SQL statement
4. Right-click the title bar and click Datasheet View
5. Imagine a customer wants to purchase a home (single family or townhouse) around Silver Spring (you will have a choice between including the cities of Silver Spring, Rockville, or Chevy Chase!) (I know guys, if you don't live in the Washington Metropolitan area, you are not supposed to know what cities are around Silver Spring :)) or the ZIP codes from 20950 to 20920). We are going to find out that Microsoft Access may not always write the SQL statement the way you want :).
   In the bottom section of the window, under ZIP Code, click its corresponding Criteria box and type BETWEEN 20850 AND 20920
6. Right-click the title bar and click SQL View
   

   SELECT 	Properties1.[Property #], 
   	Properties1.[Property Type],
   	Properties1.City, 
   	Properties1.State, 
   	Properties1.[ZIP Code], 
   	Properties1.Bedrooms, 
   	Properties1.Bathrooms, 
   	Properties1.Stories, 
   	Properties1.[Year Built], 
   	Properties1.Condition, 
   	Properties1.[Market Value]
   FROM 	Properties1
   WHERE 	(((Properties1.[ZIP Code]) Between 20850 And 20920));

7. Right-click the title bar and click Datasheet View:
    
   
8. Right-click the title bar and click Design View
9. Click the Criteria box under Property Type and type "single family"
10. Press the down arrow key to access its or box and type "townhouse"
     
    
11. Right-click the title bar and click SQL View to examine the statement:
    

    SELECT 	Properties1.[Property #], 
    	Properties1.[Property Type], 
    	Properties1.City, 
    	Properties1.State, 
    	Properties1.[ZIP Code], 
    	Properties1.Bedrooms, 
    	Properties1.Bathrooms, 
    	Properties1.Stories, 
    	Properties1.[Year Built], 
    	Properties1.Condition, 
    	Properties1.[Market Value]
    FROM 	Properties1
    WHERE 	(((Properties1.[Property Type])="single family") AND 
    	 ((Properties1.[ZIP Code]) Between 20850 And 20920)) OR 
    	 (((Properties1.[Property Type])="townhouse"));

12. Right-click the title bar and click Datasheet View Transmission combo box, select Automatic:
     
    
13. Notice that the list includes properties in DC, VA, and WV, which is not what we want.
    Right-click the title bar and click SQL View
14. Change the statement as follows:
    

    SELECT 	Properties1.[Property #], 
    	Properties1.[Property Type], 
    	Properties1.City, 
    	Properties1.State, 
    	Properties1.[ZIP Code], 
    	Properties1.Bedrooms, 
    	Properties1.Bathrooms, 
    	Properties1.Stories, 
    	Properties1.[Year Built], 
    	Properties1.Condition, 
    	Properties1.[Market Value]
    FROM 	Properties1
    WHERE 	((Properties1.[Property Type] = "single family") OR 
    	 (Properties1.[Property Type] = "townhouse")) AND 
    	 (Properties1.[ZIP Code] Between 20850 And 20920);

15. Right-click the title bar and click Datasheet View Transmission combo box, select Automatic:
     
    
16. Close the query
17. When asked whether you want to save, click No

Lesson Summary

MCAS: Using Microsoft Office Access 2007 Topics

Exercises

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open the Customers form
3. Use Filter By Form to show a list of customers from Maryland or Virginia
4. Show a list of customers from DC whose accounts were created on the 5th of the month
5. Open the Employees form, show a list of employees who are allowed to create new account and whose last names start with either y or h

Watts A Loan

1. Open the Watts A Loan database
2. In the Employees form, find the employees whose title include the word manager and whose home phone number uses the area code 301

World Statistics

1. Open the World Statistics1 database
2. Open the Countries form
3. Show the countries that got their independence before 1960 and whose common name ends with ia
4. Show the countries whose common name includes the letter t and whose national holiday is in July

US Senate

1. Open the US Senate1 database
2. Open the Senators table
3. Show a list of senators from both New England and the Pacific (do some research to find out what states are part of New England and Pacific)