 |
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
- Start Microsoft Access and open the Altair Realtors2 database from the
previous lesson
- In the Navigation Pane, double-click the Properties table
- 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
- To see the list of single family homes in Montgomery county, in the Locality
column, right-click Montgomery County and click Equals "Montgomery County"
- In the Property Type column, right-click Single Family and click "Single Family"

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

- 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
- To see the list of town-homes in Maryland, on the table, click the down-pointing button on the right side of Property
Type
- In the window that appears, click (Select All)
- Click Townhouse

- Click OK

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

- Click OK

- Close the table without saving it
- Open the Monson University1 database from the
previous lesson
- On the Ribbon, click Create and click Query Design
- In the Show Tabler dialog box, click Students, click Add, and click
Close
- In the list of items, double-click StudentNumber, FirstName, LastName,
Gender, City, and State
- Right-click the title bar of the query and click SQL View
- 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;
- Preview the results in the Datasheet View
-
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
- InIn the Home tab of the Ribbon, in the Sort & Filter section, click
Advanced and click Filter By Form
- To see the list of male students coming from Virginia, click the cell
below Gender
- Click the arrow of its combo box and select Male
- Click the box below State, then click the arrow of its combo box and
select VA

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

- Close the query without saving it
- Open the Ceil Inn3 database from Lesson 36
- To start a new query, on the Ribbon, click Create and click Query Design
- In the Show Table dialog box, click Rooms, click Add, and click Close
- In the list of items, double-click RoomNumber, RoomType, BedType, and
DailyRate
- Preview the results in the Datasheet View
- 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
- 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
- Type bedroom
- Click the box at the intersection of BedType and Criteria
- Type king

- To preview the results, on the Ribbon, click the Run
button

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

- Previous the results in Print Preview

- 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
- In the Property Sheet, click the All tab and change the following
characteristics:
Filter: (RoomType = "Bedroom") AND (RoomStatus = "Available")
Filter On Load: Yes

- Display the report in Print Preview

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