Home

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:

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. Start Microsoft Access
  2. From the resources that accompany our lessons, open the Altair Realtors2 database
  3. On the Ribbon, click Create and, in the Queries section, click Query Design
  4. In the list of tables, click Properties, click Add, and click Close
  5. 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:

Logical Conjunction

To perform filtering by selecting this or that value:

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:

OR

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

OR

To remove the filter, 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 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:

OR

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:

Filter by Form

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

OR

 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. On the Ribbon, click Home. 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:

OR

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:

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
     
    Query
  3. Right-click the title bar of the window and click SQL View to see the SQL code
    SELECT 	Properties.[Property #],
	Properties.[Property Type], 
	Properties.City, 
	Properties.State, 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.Condition, 
	Properties.[Market Value]
FROM 	Properties1
WHERE 	(((Properties.State)="VA")) OR (((Properties.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 City and click Sort A To Z
     
    Query
  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:

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:

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:

Evaluating the combined statement would produce the following results:

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. On the Ribbon, click Create
  2. In the Queries section, click Query Design
  3. In the list of tables, click Properties, click Add, and click Close
  4. 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:

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:

AND

After checking the value of the first field, click OK:

AND

Access another field and select one of its values. Here is an example:

AND

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

AND

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, the query, or the form. On the Ribbon, click Home. 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:

AND

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:

AND

To filter using a grid, open the table or form. On the Ribbon, click Home. In the Sort & Filter section of the Ribbon, click Advanced -> 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:

Filtering

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

AND

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
     
    AND
  3. Right-click the title bar of the window and click SQL View to see the SQL code
    SELECT 	Properties.[Property #], 
	Properties.[Property Type], 
	Properties.City, Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.[Year Built], 
	Properties.Condition, 
	Properties.[Market Value]
FROM 	Properties1
WHERE 	(((Properties.City)="Silver Spring") AND 
	 ((Properties.[Market Value])<=500000));
  4. Right-click the title bar of the window and click Datasheet View
     
    AND
  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 Female:

Combination of Logical Operators

 Then, click the Lives in a Single-Parent Home? check box:

Combination of Logical Operators

By clicking Toggle Filter, this would produce 15 records:

Students

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 Male gender" AND "Emergency Number contains something for each Male gender". To create the criteria using Filter by Form on a form, for the first criterion, you would select Male in the Gender combo box. Then, in the Home Phone field, type IS NOT NULL (which means that this field must not be empty for the criterion to apply):

Combination of Logical Operators

Then, click the OR tab. Again, in the Gender combo box, select Male. In the Emrgcy Phone field, type IS NOT NULL:

Combination of Logical Operators

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

Combination of Logical Operators

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 (called the guardian), we want to make sure that we have a corresponding emergency name. Such an expression can be created as follows:

"<'Gender=Female' AND 'Home Phone IS NULL'> OR <'Gender=Female' 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 Female Gender and, in the Home Phone field, we can type IS NULL:

Combination of Logical Operators

Then, we click the OR tab and, in the Gender combo box we select Female again. In the Emergency Name, we would type IS NULL and, in the Emergency Phone, we type IS NULL:

Combination of Logical Operators

Practical Learning: Combining Logical operators
  1. On the Ribbon, click Create
  2. In the Queries section, click Query Design
  3. In the list of tables, click Properties, click Add, and click Close
  4. From the list of fields, double-click Property #, Property Type, City, State, ZIP Code, Bedrooms, Bathrooms, Stories, Year Built, Condition, and Market Value
  5. Right-click the title bar and click SQL View to see the SQL statement
  6. Right-click the title bar and click Datasheet View
  7. 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!) (If you don't live in the Washington DC 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
  8. Right-click the title bar and click SQL View
    SELECT 	Properties.[Property #], 
	Properties.[Property Type],
	Properties.City, 
	Properties.State, 
	Properties.[ZIP Code], 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.Stories, 
	Properties.[Year Built], 
	Properties.Condition, 
	Properties.[Market Value]
FROM 	Properties1
WHERE 	(((Properties.[ZIP Code]) Between 20850 And 20920));
  9. Right-click the title bar and click Datasheet View:
     
  10. Right-click the title bar and click Design View
  11. Click the Criteria box under Property Type and type "single family"
  12. Press the down arrow key to access its or box and type "townhouse"
     
    Query
  13. Right-click the title bar and click SQL View to examine the statement:
    SELECT 	Properties.[Property #], 
	Properties.[Property Type], 
	Properties.City, 
	Properties.State, 
	Properties.[ZIP Code], 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.Stories, 
	Properties.[Year Built], 
	Properties.Condition, 
	Properties.[Market Value]
FROM 	Properties1
WHERE 	(((Properties.[Property Type])="single family") AND 
	 ((Properties.[ZIP Code]) Between 20850 And 20920)) OR 
	 (((Properties.[Property Type])="townhouse"));
  14. Right-click the title bar and click Datasheet View Transmission combo box, select Automatic:
     
    Query
  15. 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
  16. Change the statement as follows:
    SELECT 	Properties.[Property #], 
	Properties.[Property Type], 
	Properties.City, 
	Properties.State, 
	Properties.[ZIP Code], 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.Stories, 
	Properties.[Year Built], 
	Properties.Condition, 
	Properties.[Market Value]
FROM 	Properties
WHERE 	((Properties.[Property Type] = "single family") OR 
	 (Properties.[Property Type] = "townhouse")) AND 
	 (Properties.[ZIP Code] Between 20850 And 20920);
  17. Right-click the title bar and click Datasheet View Transmission combo box, select Automatic:
     
    Combinations
  18. Close the query
  19. When asked whether you want to save, click No

Lesson Summary

 

MCAS: Using Microsoft Office Access 2007 Topics
S22 Switch between object views
 

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
     
    Employees

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)
Previous Copyright © 2010-2019, FunctionX Next