Home

Data Analysis and Logical Conjunctions

Fundamentals of Conjunctions

Introduction

A logical conjunction is a condition in which two expressions must be true for the whole statement to be true. A logical conjunction can be created on a table, a query, a form, or a report, in either their regular view or in design. Microsoft Access provides many options to create and execute a logical conjunction.

Practical Learning: Introducing Logical Conjunction

  1. Start Microsoft Access and open the Altair Realtors2 database from the previous lesson
  2. In the Navigation Pane, double-click the Properties table
  3. 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: Orange, Accent 2, Lighter 80% (Theme Colors: 6th column, 2nd row)
    Background Color: Dark Red (Standard Colors: 1st column, 7th row)
    Alternate Row Color: Maroon 4 (Standard Colors: 6th column, 5th row)

    Introducing Logical Disjunction

Filtering by Selection and Conjunctions

Filtering by selection is one of the ways, and one of the easiest ways you can create a logical conjunction for data analysis. To proceed, display the table or form in the Datasheet View, the form in the Form View, the report in Report View, or the form or rerport in Layout View. As seen in Lesson 34, either right-click a value in the column of your choice and select one of the filter options, or click the desired value and use the desired option from the Selection button in the Ribbon. Then do the same for another column of your choice.

Practical Learning: Using Logical Expressions in a Disjunction

  1. To see the list of single family homes in Montgomery county, in the Locality column, right-click Montgomery County and click Equals "Montgomery County"
  2. In the Property Type column, right-click Single Family and click "Single Family"

    Filtering by Selection and Conjunctions

  3. In the Sort & Filter section of the Ribbon, click the Toggle Filter button
  4. In the Condition column, right-click Excellent and click Equals "Excellent"
  5. In the Market Value column, right-click $505 and click Less Than Or Equal To $505...

    Filtering by Selection and Conjunctions

  6. In the bottom side of the table, click the Filtered button

A Logical Conjunction When Filtering by Value

Filtering by value is another option to apply a conjunctive filter in a list. To atart:

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

In both cases, a window would come up with a check box for each values of the column you selected. Here is an example:

Filtering Records - Filtering By Value

Put a check mark on a value other than (Select All). After that first selection, click OK. Perform the same actions in another column, that is, make a value selection on another column and click OK. This means that the selections must be made on two different columns.

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 Conjunctions

  1. To see the list of town-homes in Maryland, on the table, click the down-pointing button on the right side of Property Type
  2. In the window that appears, click (Select All)
  3. Click Townhouse

    Filtering by Value for Logical Conjunctions

  4. Click OK

    Filtering by Value for Logical Conjunctions

  5. Click the down-pointing arrow on the right side of State
  6. In the window that appears, click (Select All) and click MD

    Filtering by Value for Logical Conjunctions

  7. Click OK

    Filtering by Value for Logical Conjunctions

  8. Close the table without saving it
  9. Open the Monson University1 database from the previous lesson
  10. On the Ribbon, click Create and click Query Design
  11. In the Show Tabler dialog box, click Students, click Add, and click Close
  12. In the list of items, double-click StudentNumber, FirstName, LastName, Gender, City, and State
  13. Right-click the title bar of the query and click SQL View
  14. Change the SQL statement as follows:
    SELECT Students.StudentNumber AS [Student #],
           Students.FirstName AS [First Name],
           Students.LastName AS [Last Name],
           Students.Gender,
           Students.City,
           Students.State
    FROM Students;
  15. Preview the results in the Datasheet View
  16. In the Home tab of the Ribbon, change the following characteristics:
    Font Name: Constantia (if you don't have that font, select Times New Roman)
    Font Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)
    Background Color: Green, Accent 6, Darker 25% (Theme Colors: 10th column, 5th row)
    Alternate Row Color: Green, Accent 6, Darker 50% (Theme Colors: 10th column, 6th row)

Filtering by Form for a Logical Conjunction

As seen in the previous lesson, to filter by form, open the table, query, or form in its regular view. Switch to the Filter By Form window as we saw in the previous lesson. To create a conjunction when filtering by form, click the combo box under a desired field and select the desired value. Then,  click the combo box under the other desired field and select the second desired value. After making the selections, apply the filter. The resulting list would include the records that use both of the selected values.

Practical Learning: Filtering by Form for Logical Conjunctions

  1. InIn the Home tab of the Ribbon, in the Sort & Filter section, click Advanced and click Filter By Form
  2. To see the list of male students coming from Virginia, click the cell below Gender
  3. Click the arrow of its combo box and select Male
  4. Click the box below State, then click the arrow of its combo box and select VA

    Filtering by Form for Logical Conjunctions

  5. To apply the filter, right-click an empty area in the window and click Apply Filter/Sort

    Filtering by Form for Logical Conjunctions

  6. Close the query without saving it
  7. Open the Ceil Inn3 database from Lesson 36
  8. To start a new query, on the Ribbon, click Create and click Query Design
  9. In the Show Table dialog box, click Rooms, click Add, and click Close
  10. In the list of items, double-click RoomNumber, RoomType, BedType, and DailyRate
  11. Preview the results in the Datasheet View
  12. Return to the Design View

A Logical Conjunction in a Query Design

Remember that, to create a filter in the Design View of a query, you use the Criteria box of a field. To create a logical conjunction, click the Criteria box of the other field and type the second Boolean expression. This means that the sub-expressions of a logical conjunction should be made on the same Criteria row but for different fields.

Practical Learning: Creating a Logical Conjunction in a Query Design

  1. To create a list of bedrooms that have a queen bed, in the bottom side of the window, click the box at the intersection of RoomType and Criteria
  2. Type bedroom
  3. Click the box at the intersection of BedType and Criteria
  4. Type king

    Creating a Logical Conjunction in a Query Design

  5. To preview the results, on the Ribbon, click the Run Run button

    Introduction to Conditions

  6. Close the query
  7. When asked whether you wan to save, click No
  8. To start a new report, on the Ribbon, click Create and click Report Design
  9. In the Property Sheet, click the All tab.
    Click Record Source, then click its arrow button and select Rooms
  10. Set the Caption to Ceil Inn - Available Bedrooms
  11. On the Ribbon, click Design and click Add Existing Fields
  12. In the Field List, double-click RoomNumber, RoomType, BedType, DailyRate, and RoomStatus
  13. Save the report as Available Bedrooms
  14. Design the report like a tabular one

    A Logical Conjunction in a Query Design

  15. Previous the results in Print Preview

    Using Logical Conjunctions in SQL

  16. Return to the Design View

A Logical Conjunction on an Object Filter or the Query Builder

To set a conditional conjunction on a table, an existing query, or a form/report that already has a record source, or if you are setting up the Record Source of a new form or report, display the window for data selection and proceed as if you were working in the Design View of a query.

An Expression for a Logical Conjunction

Introduction

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

sub-expression1 AND sub-expression2

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

column-name operator value

This means that a logical conjunction is formulated as:

column-name1 operator value1 AND column-name2 operator value2

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

(column-name1 operator value1) AND ( column-name2 operator value2)

Creating a Conjunction Criterion in the Design View

To create a filtered conjunction for a table, a query, a form, or a report in Design View, access its Property Sheet. Click Filter and type a conjunctive 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 Conjunction Criterion in the Design View

  1. In the  Property Sheet, click the All tab and change the following characteristics:
    Filter: (RoomType = "Bedroom") AND (RoomStatus = "Available")
    Filter On Load: Yes

    Creating a Conjunction Criterion in the Design View

  2. Display the report in Print Preview

    Creating a Conjunction Criterion in the Design View

  3. Save and close the report

Logical Conjunctions in the SQL

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

WHERE sub-expression1 AND sub-expression2

Remember that each sub-expression is in the form:

field-name Operator Value

Here is an example:

SELECT PropertyNumber,
       City,
       Locality,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       MarketValue
FROM   Properties
WHERE  PropertyType = "single family" AND Condition = "excellent";

Remember that it is a good idea to put each sub-expression in parentheses. Here is an example:

SELECT PropertyNumber,
       City,
       Locality,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       MarketValue
FROM   Properties
WHERE (PropertyType = "single family") AND (Condition = "excellent");
 
 
 

Creating a Many-Field Logical Conjunction

Introduction

So far, we included only two sub-expressions for a conjunction. Actually, you can include as many sub-expressions as you want. At the end, the database engine will include all records that include all of the values.

Filtering by Value

To create a logical conjunction that includes more than two expressions, if you are filtering by value, display the list of check boxes for one field, click the value of the desired check box, and click OK. Proceed to another field to make the next check selection and click OK. Continue with each additional field for the conjunction.

Practical Learning: Creating a Many-Field Conjunction in Filtering by Value

  1. Open the Altair Realtors2 database used earlier
  2. In the Navigation Pane, double-click the Properties table
  3. 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)
  4. Click any cell below Locality
  5. On the Ribbon, click the Filter button Filter
  6. In the window, click (Select All) to remove its check mark
  7. Click Prince Geoge's County

    Creating a Many-Field Conjunction in Filtering by Value

  8. Click OK
  9. Click any cell below Property Type
  10. On the Ribbon, click the Filter button Filter
  11. In the window, click (Select All) to remove its check mark and click Single Family

    Creating a Many-Field Conjunction When Filtering by Value

  12. Click OK
  13. Click any cell below Condition
  14. On the Ribbon, click the Filter button Filter
  15. In the window, click (Select All) to remove its check mark and click Excellent

    Creating a Many-Field Conjunction When Filtering by Value

  16. Click OK

    Creating a Many-Field Conjunction When Filtering by Value

  17. Close the table without saving it
  18. On the Ribbon, click Create and click Query Design
  19. In the Show Table dialog box, make sure Properties is selected or click it.
    Click Add and click Close
  20. In the list of items, double-click PropertyNumber, City, State, PropertyType, Bedrooms, and MarketValue
  21. Preview the results in the Datasheet View
  22. 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 80% (Theme Colors: 10th column, 2nd row)
    Alternate Row Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)

Filtering by Form

To create a logical conjunction that involves more than one value, if you are filtering by form, in the combo box of the desired field, select the desired value. Access the combo box of another field and select the desired value. Keep selecting the additional values from the desired fields. After making all the selections, apply the filter.

Practical Learning: Creating a Conjunctions When Filtering by Form

  1. On the Ribbon, click Advanced and click Filter By Form
  2. Click the box below State, then click the arrow of its combo box and select DC
  3. Click the box below PropertyType, then click the arrow of its combo box and select Condominium
  4. Click the box below Bedrooms, then click the arrow of its combo box and select 2

    Creating a Conjunctions When Filtering by Form

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

    Creating Many Disjunctions When Filtering by Value

The SQL and Logical Conjunctions

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

Practical Learning: Creating a Logical Conjunction in SQL

  1. Right-click the Query1 tab and click SQL View
  2. Change the SQL statement as follows:
    SELECT PropertyNumber,
           City,
           State,
           PropertyType,
           Bedrooms AS Beds,
           Bathrooms AS Baths,
           YearBuilt,
           MarketValue
    FROM Properties
    WHERE (State = "md") AND (PropertyType = "single family") AND (Bedrooms = 4);
  3. Preview the result in the Datasheet View

    Creating a Logical Conjunction in SQL

  4. Display the Design View of the query

Topics on Creating and Using Conjunctions

Logical Expressions and Conjunctions

Each sub-expression of a logical conjunction can be a logical operation that can produce True/False or any value you want.

Practical Learning: Using Expressions in a Logical Conjunction

  1. In the bottom section of the table, click the Criteria box for Bedrooms and type >= 4
  2. Click the Criteria box for MarketValue and type >= 650000

    Using Expressions in a Logical Conjunction

  3. Preview the results in the Datasheet View

    Using Expressions in a Logical Conjunction

  4. Right-click the tab of the window and click SQL View
    SELECT PropertyNumber,
           City,
           State,
           PropertyType,
           Bedrooms AS Beds,
           Bathrooms AS Baths,
           YearBuilt,
           MarketValue
    FROM   Properties
    WHERE  (Bedrooms IN (3,4,5)) AND (MarketValue BETWEEN 350000 And 750000);
  5. Right-click the tab of the window and click Datasheet View

    Using Expressions in a Logical Conjunction

  6. Close the query
  7. When asked whether to save it, click No

Logical Expressions and/or Functions in a Conjunction

Each sub-expression of a logical conjunction 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 Conjunction

  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, Capital, 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. Press the up arrow key and press the Space bar to remove the check mark on Show
  8. Click the next top empty box and type Year(AdmissionUnionDate)
  9. Click its corresponding Show box to remove the check mark
  10. Click the Criteria box of the same column and type BETWEEN 1815 AND 1865 (the expression means "Show the states whose names start with the letter m and and joined the Union between 1815 and 1865")

    Using Logical Expressions in a Conjunction

  11. Preview the results in the Datasheet View

    Using Logical Expressions in a Conjunction

  12. Display the Design View of the query

Sorting Records

When creating a logical conjunction, 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. In the bottom selection of the query, click the Sort box for AreaSqrMiles, then click the arrow button of that box, and select Ascending

    Creating a Logical Conjunction in SQL

  2. Preview the results in the Datasheet View

    Creating a Logical Conjunction in SQL

  3. Close Microsoft Access without saving the query
 
 
   
 

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