Exploring Data Analysis

Combinations of Operations

Overview

To further enhance your filtering capabilities, you can use Boolean algebra combined with the operators we have used so far. Besides the logical operators we know already, Boolean algebra adds extra operators used to concatenate expressions.

Boolean Algebra works on logical statements. A statement is a sentence 

Logical Conjunction: The And Operator

Imagine that 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: Filtering Using Logical Conjunction

1. Open the Bethesda Car Rental1 database and open the Cars form in Form View
2. On the main menu, click Records -> Filter -> Filter by Form 
3. To see a list of SUV cars, click the arrow of the Category combo box and select SUV
4. On the main menu, click Records -> Filter -> Apply Filter/Sort
    
   
5. After viewing the records, on the main menu, click Records -> Remove Filter/Sort
6. On the Form View toolbar, click the Filter by Form button
7. Delete the content of the Category combo box
8. Imagine that a customer wants a car that has A/C and a CD player.
   To combine two criteria for a logical conjunction, click the check box of Air Condition. Then click the check box of CD Player
    
   
9. On the Form View toolbar, click the Apply Filter button:
    
   
10. Navigate through the records and verify that each of the filtered cars has BOTH air condition and a CD Player. 
11. After viewing the records, to refresh, on the Form View toolbar, click the Remove Filter button. 
12. Right-click an empty area on the form and click Filter by Form 
13. Remove the check marks on the form
14. Imagine that another customer wants to lease a small car. To proceed, you need to find out what Economic category cars are Available
    Click the check box of Available
15. Then, in the Category combo box, select Economy
16. Right-click an empty area on the form and click Apply Filter/Sort
17. After checking what small (Economy) cars are Available, right-click on the form and click Remove Filter/Sort
18. Open the ROSH database and, from the Queries section of the Database window, open the Staff Members query
19. Switch it to Design View
20. To get the list of employees whose salaries range from $10 to $15, in the Criteria for Salary
21. Type >=10 And <=15
22. Run the query
23. To get a list of employees whose salary is greater than $10 AND live in MD, in the Criteria for Salary, type >=10
24. Then, in the Criteria for State, type =”MD”
25. Run the query
26. Switch it back to Design View and delete both criteria
27. To get a list of the employees who earn more than $11 AND are married AND (but) do not live in Maryland, in the Criteria, for the Salary field, type >=11
28. For the Criteria field of the MaritalStatus column, type True
29. For State, type <>"MD"
     
    
30. Run the query
31. Switch it to Design View and delete the Criteria values of the Salary, the MaritalStatus, and the State columns
32. Run the query

Logical Disjunction: The OR Operator

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: Filtering Using Logical Disjunction

1. The ROSH database should still be opened with the Staff Members query.
   Switch the query to Design View
2. To get a list of employees who live in Maryland OR in Virginia, for the State field, in the Criteria row, type =“MD” Or “VA”
3. Run the query and notice that staff members who live in DC are not in the list
4. Switch to Design View
5. To run the same query, in the Criteria under State, type MD
6. Press the down arrow key (that should take you to the OR field of the Criteria for State), then type VA
7. Run the query
8. Close the query without saving it
9. Open the Bethesda Car Rental1 database and click the Forms button
10. Double-click the Cars form to open it in form View
11. On the main menu, click Records -> Filter -> Filter By Form
    If the Air Condition and the CD Player check boxes are white (instead of being NULL), click each to make it appear NULL like the other check boxes. Also, if any combo box or text box displays any value, delete it
12. Imagine that a customer wants to rent a small to medium size car. These fit in the Economy and Compact categories.
    To use the OR operator, click in the Category combo box and type: ="Economy"OR"Compact"
     
    
13. On the Form View toolbar, click the Apply Filter button
14. Navigate through the list to see the small cars of Economy and Compact
15. After viewing the list, on the Form View toolbar, click the Remove Filter button 
16. Right-click an empty area on the form and click Filter by Form
17. Delete the content of the Category combo box
18. To get a list of cars that have either a Cassette player or a CD Player (or both), click the check box of Cassette
19. On the bottom left section of the form, click the Or tab
     
    
20. Click the check box of CD Player. 
21. Right-click an empty area on the form and click the Apply Filter/Sort
22. Notice that the first car has a Cassette player but not a CD Player. The second car has not Cassette player but has a CD Player. The 12th car has both a Cassette player and a CD Player
     
    
23. After viewing the records, right-click an empty on the form and click Remove Filter/Sort
24. Click the Cassette check box twice to make appear NULL like the others
25. Click the left Or tab on the lower-left section of the form and click the CD Player check box twice to make it NULL
26. Right-click anywhere on the form and click Apply Filter/Sort

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. On the other hand, imagine that, at your car rental company, a customer wants to rent a small car that has a cassette player. 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 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. The Cars form of the Bethesda Car Rental1 database should still be opened in Form View.
   On the main menu, click Records -> Filter -> Filter By Form
2. Imagine that a customer who cannot drive stick shift wants to rent a small car. This means that you must check Available Economy cars that have Automatic Transmission
   Click the Available check box
3. In the Category combo box, select Economy
4. In the Transmission combo box, select Automatic:
    
   
5. On the main menu, click Filter -> Apply Filter/Sort
    
   
6. Notice that there are only two cars available
7. After viewing the records, imagine that the customer would like to know what else you have in the smaller cars. Besides the Economy cars, you want to see the available compact cars also.
   Right-click an empty area on the form and click Filter by Form. 
8. Click the OR tab
9. Click the Available check box. In the Transmission combo box, select Automatic
10. In the Category combo box, select Compact
11. Right-click an empty area on the form and click Apply Filter/Sort
12. After viewing the records, right-click on the form and click Remove Filter/Sort
13. Close the form
14. Open the ROSH database
15. From the Queries section of the Database window, double-click the Staff Members query to open it
16. Switch it to Design View
17. To see an alphabetical list of employees who live either in Maryland or in DC, set the Sort order of LastName to Ascending
18. In the Criteria field for State, type =”MD”
19. In the OR field for City, type =”DC”
     
    
20. Run the query
21. Switch back to Design View
22. To get an alphabetical list of Maryland-resident employees who are married, delete ="DC" in the OR field for the State field
23. In the Criteria field under the MaritalStatus column, type =Yes
24. Run the query
25. To save the current query, on the menu bar, click File -> Save As...
26. Type Married Maryland Staff Members and press Enter
27. Close the Married Maryland Staff Members query

Logical Operators: Between

The Filter By Form feature allows you to filter records by assigning appropriate criteria. The Filter By Form is like a dialog box in the form of a datasheet where you decide how to set the criteria.

A technique used to filter records between ranges of values involves the use of the BETWEEN keyword. The Between comparison is usually combined with the AND operator to get a list of records between two values.

Practical Learning: Filter With Wildcards

1. The ROSH database should still be opened.
   From the Forms section of the Database window, double-click the Students form to open it in Form View
2. Right-click the Date of Birth field and click the Filter For: text box, type
   Between #1/1/1985# And #12/31/1985#
3. Press Enter
4. After viewing the records, right-click on the form and click Remove Filter/Sort
5. Close the Students form and, on the Database window, click Queries
6. Double-click the Staff Members query and switch it to Design View
7. Click the Criteria for Salary and type Between 10 And 15
8. Run the query then close it without saving it

Like Wildcards

A wildcard is a character or a group of characters that specify a criterion the database should follow to find records. When performing filtering, wildcards are usually used with the LIKE operator.

Practical Learning: Filter With Wildcards

1. To get a list of students who live in single parent homes, on the Form View toolbar, click the Filter By Form button
2. On the form, check the box for the Single Parent? field and delete any criteria in the other boxes
3. Then click the Apply Filter button on the main toolbar
4. When you have finished viewing, click the Remove Filter button on the toolbar 
5. To get a list of students whose parents are doctors, click the Filter by Form button . In the box for Parents Names, type Like Dr* 
6. When you have finished viewing, click the Remove Filter button on the Form View toolbar. 
7. Close the form. 

MOUS Topics