Characteristics of a Report
Fundamentals of Conjunctions
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
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
A Logical Conjunction When Filtering by Value
Filtering by value is another option to apply a conjunctive filter in a list. To atart:
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
SELECT Students.StudentNumber AS [Student #], Students.FirstName AS [First Name], Students.LastName AS [Last Name], Students.Gender, Students.City, Students.State FROM Students;
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
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
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
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
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
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
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
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
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);
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
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);
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.
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
|Previous||Copyright © 2002-2021, FunctionX, Inc.||Next|