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)

   

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"

   

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

   

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:

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

   

4. Click OK

   

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

   

7. Click OK

   

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

   

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

   

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

   

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

   

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

    

15. Previous the results in Print Preview

    

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

   

2. Display the report in Print Preview

   

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
6. In the window, click (Select All) to remove its check mark
7. Click Prince Geoge's County

   

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

    

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

    

16. Click OK

    

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

   

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

   

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

   

3. Preview the results in the Datasheet View

   

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

   

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")

    

11. Preview the results in the Datasheet View

    

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

   

2. Preview the results in the Datasheet View

   

3. Close Microsoft Access without saving the query