Lessons Logo

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 

that acknowledges a fact or a possibility. That fact is eventually evaluated as being true or false. There are three main types of logical statements:

  • Some statements can clearly be evaluated as being true or as being false (or at least they would hardly change). Examples include: "An hour contains 80 minutes", "You are a girl", "Gabon was colonized by Portugal", or "Argentina won the 1998 World Cup".
  • Some statements are true at one time but can change to false at another time. Examples include: "Melbourne is the capital of Australia", "It is now 12:00 PM", "My monitor is 17 inches", or "It is raining".
  • Some statements cannot be easily evaluated. Such statements could be vague. Examples include: "This girl is attractive", "It is going to be a sunny day tomorrow". Some other statements, although they can logically be evaluated, because of the way they are stated, they appear as vague. Examples include: "Santiago is a large country" (first of all, Santiago is not a country; even if Santiago were a country, what is the criterion for evaluating that a country is large?).

After a statement is formulated, it is evaluated as being true or false. We have already applied this concept during data analysis. Imagine that, in your database of students, you want to see a list of students whose records indicate a home telephone number. The statement to be evaluated is:

  • This student's record indicates a home phone number

A statement such as "This student's record indicates a home phone number" is evaluated as being true or false. If the record of the student referred to holds a home phone number, the statement is true. If the record referred to does not display a home telephone number, the statement is false. We have already seen that such a record can be filtered with an expression such as =Not Null.

We know that, in our database, besides the home telephone number, a student’s record also can provide an emergency telephone number (a number to dial if something happens to the student). If the record of a student does not have a home telephone number that we want to use to contact a student, we can just find out whether an emergency number was provided. Therefore, we can also filter records using a statement such as:

  • This student's record provides an emergency phone number

Now, in case we want to contact somebody for the student, if a student's record does not display a home telephone, alternatively, we can use the emergency telephone. This means that we need to find out if the student's record displays either one of those numbers.

 

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

 
S28 Apply and remove filters (filter by form and filter by selection)
S29 Specify criteria in a query
 

Previous Copyright © 2002-2009 FunctionX, Inc. Next