Fundamentals of Disjunctions

Introduction

A logical disjunction is a technique of selecting records that match any of some provided values. This means that you must provide (at least) two values as a filter and the database engine would select only the records that include either of those values. Once again, Microsoft Access provides various tools to perform data analysis that includes logical disjunction.

One of the primary differences between a disjunction and a conjunction is that a disjunction can be applied to different values of the same field or values of different fields while a conjunction should always be used on different fields whether they use the same type or not.

As seen for conjunctions, Microsoft Access provides many options to create and execute a logical disjunction.

Practical Learning: Introducing Logical Disjunction

1. Start Microsoft Access
2. In the list of files, click Altair Realtors2 from the previous lesson
3. In the Navigation Pane, right-click the Properties table and click Copy
4. Right-click an unoccupied area of the Navigation Pane and click Paste
5. Set the name to Ready for Sale and click OK
6. In the Navigation Pane, double-click the Ready for Sale table
7. In the Home tab of the Ribbon, change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Font Color: Green 1 (Standard Colors: 7th column, 2nd row)
   Background Color: More Colors: Red: 210, Green: 75, Blue: 25
   Alternate Row Color: More Colors: Red: 235, Green, 135, Blue: 100

   

Filtering by Value for Logical Disjunctions

The filtering by value used in previous lessons is one of the most convenient ways to create a logical disjunction. To create this criterion, display the filter by value window. To apply a logical disjunction, put the check mark on at least two values excluding the (Select All) option.  This means that the values of a logical disjunction should be the check boxes of the same field in filtering by value.

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, 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.

Practical Learning: Filtering by Value for Logical Disjunctions

1. In the query, click the double-pointing button on the right side of Condition
2. In the window that appears, click (Select All)
3. Click Excellent and click Good Shape

   

4. Click OK

   

5. Close the query
6. When asked whether you want to save, click Yes

A Logical Disjunction in a Query Design

If you are working in the Design View of a query, to create a single filter, use the Criteria box of the desired field. To let you create a logical disjunction, the bottom side of the Design View of a query provides an additional row named or. Based on this, to create a logical disjunction, set the first filter in the Criteria box for one field. Click the or box for the same field and type the second criterion. This means that the sub-expressions of a logical disjunction should be on different rows (Criteria and Or) but for the same column. Once you are ready, preview the query in the Datasheet View.

Practical Learning: Creating a Logical Disjunction in a Query Design

1. On the Ribbon, click Create and, in the Queries section, click Query Design
2. In the list of tables, click Properties, click Add, and click Close
3. From the list of fields, double-click PropertyNumber,  City, PropertyType, Bedrooms, Bathrooms, Condition, and Market Value
4. In the bottom side of the window, click the box at the intersection of PropertyType and Criteria
5. Type single family
6. Press the down arrow key and type townhouse

   

7. To preview the results, on the Ribbon, click the Run button
8. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Background Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)
   Alternate Row Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)

   

9. Close the query
10. When asked whether you wan to save, click No

Filtering by Form for Logical Disjunctions

Filtering by Form is another very convenient tool to analyze data using a disjunction. To filter by form, open the table, query, or form in their regular view. In the Sort & Filter section of the Ribbon, click Advanced and click Filter By Form. A window will appear with two tabs at the bottom. One tab is labeled Look For and the other tab is labeld Or:

To create a disjunction when filtering by form, click the combo box under the desired field and select the desired value. Then, in the bottom side of the window, click the Or tab. Click the combo box under the same field and select the other value. After making the selections, apply the filter. The resulting list would include the records that use either of the selected values.

Practical Learning: Filtering by Form for Logical Disjunctions

1. On the Ribbon, click File and click Open
2. In the list of files, click StatesStatistics3 from the previous lesson
3. In the Navigation Pane, double-click the States table
4. In the Home tab of the Ribbon, in the Sort & Filter section, click Advanced and click Filter By Form
5. Click the box below Region, then click the arrow of its combo box and select East South Central

   

6. In the bottom side of the window, click Or
7. Click the box below Region, then click the arrow of its combo box and select West North Central

   

8. To apply the filter, on the Ribbon, click Advanced and click Apply Filter/Sort

   

9. Close the table without sa

A Logical Disjunction on an Object Filter

To set a conditional disjunction on a table, an existing query, or a form/report that already has a record source, display the table or query in Datasheet View, a form in Form View or in Layout View, or a report in Report View or in Layout View. In the Sort & Filter section of the Ribbon, click Advanced and click Advanced Filter/Sort... Create the disjunction as we saw for the Design View of a query, using the Criteria and the Or boxes of the desired field.

An Expression for a Logical Disjunction

Introduction

The Boolean operator used to create a logical disjunction is named OR. Its expression uses the following formula:

sub-expression1 OR sub-expression2

As you can see, you create a sub-expression on each side of the OR operator. Each sub-expression is a logical operation, the same type we intrduced in Lesson 34 as:

column-name operator value

So an expression for a logical disjunction is actually:

column-name1 operator value1 OR column-name2 operator value2

To make the expression easy to read, you should put each sub-expression in parentheses.

Creating a Disjunction Criterion in the Design View

To create a filtered disjunction for a table, a form, or a report in Design View, access its Property Sheet. Click Filter and type an expression as seen above. Remember that if you want the filter to apply immediately when you display the object in its regular view, set the Filter On Load property to Yes.

Practical Learning: Creating a Disjunction in a Design View

1. Open the Cruise3 database from Lesson 37
2. In the Navigation Pane, right-click the Employees form and click Copy
3. Right-click an unoccupied area of the Navigation Pane and click Paste
4. Set the name as Staff Members - Administration and Security and click OK
5. In the Navigation Pane, right-click Staff Members - Administration and Security and click Design View
6. In the Property Sheet, click the All tab and change the following characteristics:
   Filter: (DepartmentCode = "ADMIN") OR (DepartmentCode = "SCRTY")
   Filter On Load: Yes

   

7. Preview the form in the Form View

   

8. Save and close the form

Logical Disjunctions in the SQL

As seen for other filters, in the SQL, a logical disjunction is created in the WHERE clause as follows:

WHERE expression1 OR expression2

Remember that each expression is in the form:

field-name Operator Value

 This means that you must specify a column, the operator that performs the filtering, and the value applied to the column.

Practical Learning: Using Logical Disjunctions in SQL

1. Open the Alter Realtors2 database used ealier
2. On the Ribbon, click Create and click Query Design
3. On the Show Table dialog box, click Close
4. Right-click the body of the window and click SQL View
5. Write the SQL statement as follows:

   SELECT PropertyNumber,
          City,
          Locality,
          PropertyType,
          Condition,
          MarketValue
   FROM   Properties
   WHERE  Locality="Montgomery County" OR Locality="Prince George's County";

6. Preview the results in the Datasheet View
7. In the Home tab of the Ribbon, change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Background Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)
   Alternate Row Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)

   

8. Close the query without saving it
9. Open the Ceil Inn3 database from the previous lesson
10. To start a new report, on the Ribbon, click Create and click Report Design
11. In the Property Sheet, click the All tab.
    Click Record Source, then click its ellipsis button
12. On the Show Table dialog box, click Close
13. Right-click the title bar of the window and click SQL View
14. Change the SQL statement as follows:

    SELECT *
    FROM Rooms
    WHERE (RoomType = "Bedroom") OR (RoomType = "Studio");

15. To close and return to the report, on the Ribbon, click the Close button
16. When asked whether to save it, click Yes
17. Save the report as Sleeping Rooms
18. On the Ribbon, click Design and click Add Existing Fields
19. In the Field List, double-click RoomNumber, RoomType, BedType, DailyRate, and RoomStatus
20. Design the report like a tabular one

    

21. Previous the results in Print Preview

    

22. Save and close the report

Creating Many Logical Disjunctions

Introduction

So far, we have created only two expressions for a disjunction. Indeed, you can include as many expressions as you want. You have many options. For the result, the database engine will include all records that include any of the values.

Filtering by Value

To create a logical disjunction that includes more than two expressions, if you are filtering by value, include a check mark to each of the values of the field. After making your selections, click OK.

Practical Learning: Creating Many Disjunctions When Filtering by Value

1. Open the StatesStatistics3 database from the previous lesson
2. In the Navigation Pane, double-click the States table
3. In the Home tab of the Ribbon, change the following characteristics:
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)
4. Click any cell below Region
5. On the Ribbon, click the Filter button
6. In the window, click (Select All) to remove its check mark
7. Click East North Central, East South Central, West North Central, and West South Central:

   

8. Click OK
9. Preview the result in the Datasheet View

   

10. Close the table without saving it

Filtering by Form

To create a logical disjunction with many expressions, if you are filtering by form, in the combo box of the desired field, select the desired value. In the bottom side of the window, click the Or tab and select the second value in the combo box of the intended field. Keep clicking Or in the bottom section of the window and selecting the additional values. After making the selections, apply the filter.

Practical Learning: Creating Many Disjunctions When Filtering by Value

1. Open the Alter Realtors2 database used ealier
2. On the Ribbon, click Create and click Query Design
3. On the Show Table dialog box, click Properties, click Add, and click Close
4. In the top list, double-click PropertyNumber, PropertyType, City, State, Condition, and MarketValuethe
5. Preview the results in the Datasheet View
6. In the Home tab of the Ribbon, change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Background Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)
   Alternate Row Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)

   

7. On the Ribbon, click Advanced and click Filter By Form
8. Click the box below State, then click the arrow of its combo box and select DC

   

9. In the bottom side, click the Or tab
10. Still in the State column, click the arrow of its combo box and select MD

    

11. In the bottom side, click the right Or tab
12. In the State combo box, select VA

    

13. Right-click inside the window and click Apply Filter/Sort to see the houses on sale in the Washington metropolitan area:

    

The SQL and Logical Disjunctions

To create a logical disjunction with many expressions in SQL, add as many logical expressions as you want and separate them with OR operators. Although the parentheses are not required, they can make your expression easier to read.

Practical Learning: Creating a Logical Disjunction in SQL

1. Right-click the Query1 tab and click SQL View
2. Change the SQL statement as follows:

   SELECT PropertyNumber,
          City,
          PropertyType,
          Condition,
          MarketValue
   FROM   Properties
   WHERE (City = "Rockville") OR (City = "Bethesda") OR (City = "Silver Spring")

3. Preview the result in the Datasheet View

   

4. Close the query without saving it

Logical Disjunctions and Different Fields

Introduction

So far, we have applied all our logical disjunctions to only one field. This is not a rule. That technique is used when you want to view records that include only some specific values of one field. In reality, you can create a disjunction that involves as many fields as you want. You can create the logical expression in filtering by form or in Design View.

Filtering by Form

To create a logical disjunction that involves many fields, if you are filtering by form, in the combo box of the desired field, select the desired value. In the bottom side of the window, click the Or tab. In the top side of the window, click a cell below another column header, then click the arrow of its combo box and select a value. After making the selections, apply the filter.

Practical Learning: Filtering by Form in Different Fields

1. In the Navigation Pane, double-click the Properties table
2. On the Ribbon, click Advanced and click Filter By Form
3. Click the cell below City, then click the arrow of its combo box and select Martinsburg
4. In the bottom side of the window, click the Or tab
5. Click the cell below State, then click the arrow of its combo box and select MD (this is for a customer who is considering homes in any part of Maryland but also would consider properties in Martinsburg or the East part of West Virginia)
6. Preview the result in the Datasheet View

   

7. Close the table without saving it

Filtering by Design

To create a many-fields-based logical disjunction in the Design View of a query, an Object Filter, or the Query Builder, in the bottom part of the window, the Criteria box for one of the field and type a logical expression. Click the Or box for another field and type the desired expression.

Practical Learning: Filtering by Form in Different Fields

1. In the Navigation Pane, right-click the Houses in Columbia, Montgomery County, and DC form and click Design View
2. In the Property Sheet, click Record Source and click the ellipsis button
3. In the Show Table dialog box, click Properties, click Add, and click Close
4. In the list of fields, double-click PropertyNumber, City, Locality, State, Property Type, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, and MarketValue
5. In the bottom side of the window, click the Criteria box for City and type columbia
6. Click the Or box that corresponds to Locality and type montgomery county
7. Click the Or box that corresponds to State, press the down-arrow key and type dc

   

8. To return to the form, on the Ribbon, click the Close button
9. When asked whether you want to save, click Yes
10. Switch the form to Form View

    

11. Save and close the form

Using SQL

In the SQL, to create a logical disjunction for many fields, create a logical expressions for each field and the desired value. Separate the expressions with OR operators. Here is an example:

SELECT RoomNumber,
       RoomType,
       BedType, 
       DailyRate, 
       RoomStatus
FROM   Rooms
WHERE  RoomType = "Bedroom" OR BedType = "Queen";

Remember that, to make your code easy to read, you should include each expression in parentheses.

Topics on Creating and Using Disjunctions

Logical Disjunctions IN Filters

An IN operation is just a variant of a logical disjunction that involves only one field. When creating an IN filter, the values you specify in the parentheses play the same role as if the name of the field equal to one of the values were combined with the OR operator. As a result, this statement:

SELECT PropertyNumber,
       City,
       PropertyType,
       Condition,
       MarketValue
FROM Properties
WHERE (City = "Rockville") OR (City = "Bethesda") OR (City = "Silver Spring")

is the same as:

SELECT PropertyNumber,
       City,
       PropertyType,
       Condition,
       MarketValue
FROM Properties
WHERE City IN ("Rockville", "Bethesda", "Silver Spring");

Logical Disjunctions and Different Values

In a logical disjunction, you can use expressions that use different types of values. This is possible because, as mentioned above, you can use different fields. In the same way, you can include an expression that checks for null or not null values. The expression is formulated normally. The final result will include the records that are null and those that include the other specified values.

Practical Learning: Using Logical Expressions in a Disjunction

1. On the Ribbon, click Create and click Query Design
2. In the Show Table dialog box, click Properties, click Add, and click Close
3. In the list, double-click PropertyNumber, City, PropertyType, Condition, Berooms, Bathrooms, and MarketValue
4. In the bottom side of the window, click the Criteria box that corresponds to the PropertyType column and type single family
5. Click the Or text box that corresponds to Bedrooms and type 3 (this means that we want a list of houses that either are single families or have three bedroomss)
6. Preview the results in the Datasheet View
7. In the Home tab of the Ribbon, change the following characteristics:
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)
8.  

   

Logical Expressions and/or Functions in a Disjunction

Each sub-expression of a logical disjunction can be a logical operation that can produce True/False or any value you want. A sub-expression can also be produced by a function.

Practical Learning: Using Logical Expressions in a Disjunction

1. Open the StatesStatistics3 database
2. On the Ribbon, click Create and click Query Design
3. In the Show Table dialog box, click States, click Add, and click Close
4. In the list, double-click StateName, AreaSqrMiles, AdmissionUnionDate, AdmissionUnionOrder, and Region
5. In the bottom side of the window, click Region, press Tab, and type Left(StateName, 1)
6. Click the Criteria box for that column and type m
7. Click the corresponding Show check box to remove the check mark
8. Click the next top empty box, type Year(AdmissionUnionDate) and press Enter
9. Click its corresponding Show box to remove the check mark
10. Click the Or box of the same column and type BETWEEN 1815 AND 1855 (the expression means "Show the states whose names start with the letter m and those that joined the Union between 1815 and 1855)

    

11. Preview the results in the Datasheet View
12. In the Home tab of the Ribbon, change the following characteristics:
    Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
    Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
    Background Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)
    Alternate Row Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)

    

Sorting Records

When creating a logical disjunction, you can sort the records using any of the fields, whether it is included in the expression or not.

Practical Learning: Using Logical Expressions in a Disjunction

1. Display the SQL View of the query and change its code as follows:

   SELECT States.StateName,
          States.AreaSqrMiles,
          States.AdmissionUnionDate,
          States.AdmissionUnionOrder, 
          States.Region
   FROM States
   WHERE (((Left([StateName], 1))="M")) OR (((Year([AdmissionUnionDate])) Between 1815 And 1855))
   ORDER BY AreaSqrMiles;

2. Preview the results in the Datasheet View

   

3. Close Microsoft Access without saving the query

Previous	Copyright © 2002-2021, FunctionX, Inc.	Next