Introduction to Conditions
As mentioned in previous lessons, data analysis resembles using a funel whose job is to select some records and exclude or ignore others. You must present a condition that the funel will follow to allow or disallow some records. A condition is also referred to as a criterion. The plural is criteria.
A criterion is formulated using a Boolean operation. Microsoft Access provides many options to specify the conditions.
Practical Learning: Introducing Data Filters
Setting Criteria in a Regular View
To set the condition by which to isolate records, if a table, a query, or a form is displaying in the Datasheet View, a form in Form View, or a report in Report View or in Layout View, in the Sort & Filter section of the Ribbon (Home tab), click the Advanced button and click Advanced Filter/Sort... A window that resembles the Design View of a query will display.
Setting Criteria on the Table, Form, and Report in Design View
To prepare the condition by which some of the records must display on a table, a form, or a report in Design View, access its Property Sheet. Click Filter and type an appropriate expression.
By default, an expression set as the filter will not apply immediately. If you want the condition to apply when the object displays in its regular view, set the Filter On Load property to Yes.
Setting Criteria on a Field
Normally, the condition for data anlasysis is set on a data field and that condition applies to the other records. This type of condition is specified in the Design View of a query or a similar window.
In the Design View, the row that controls the filters is named Criteria. To set the condition in the Design View of a query, locate the text box at the intersection of the column and the Criteria row:
WHERE is the Filter?
To assist you in creating a condition to select or exclude some records, the SQL provides the WHERE keyword. Its section, also called a WHERE clause, must come at the end of a SELECT statement. The formula to follow is:
SELECT what-field(s) FROM what-bject WHERE expression;
Remember that, to make your code easy to read, you can write the sections of the SELECT expression of different lines. In this case and by tradition, the WHERE clause is usually written on its own line (rememeber that this is not a rule but a suggestion). The formula to follow would be:
SELECT what-field(s) FROM what-bject WHERE expression;
We are already familiar with the sections before WHERE. The expression used in a condition is a Boolean expression. This means that the expression written using the formula:
column-name operator value
The column-name must be an existing column of a table or query. It is followed by an appropriate operator. The value is the value that will control the condition.
To make the statement easier to read, you should include it in parentheses after WHERE.
Primary Filtering Operations
Matching a Value
The easiest way to create a condition in data analysis is to find an exact value. This is done using the = operator. The formula to follow is:
WHERE field-name = value
If the field is:
Practical Learning: Matching a Value for Data Analysis
Negating a Condition
Sometimes you want the opposite or reverse of an expression. To assist you with this, the SQL provides the NOT operator. There are many ways to use this operator. One way is to precede the operator with the expression.
Practical Learning: Negating a Condition
Hiding a Column
Practical Learning: Not Showing a Column
The equality operation is used to find out whether two values are the same. If both values are the same, the comparison produces a True result. If they are different, the comparison renders False.
The equality operation can be illustrated as follows:
Practical Learning: Comparing For Equality
Not Equal <>
As opposed to equality, to find out if two values are not equal, use the <> operator. The formula to follow is:
value1 <> value2
The <> operator can be illustrated as follows:
The comparison for inequality follows the same concerns as equality:
The Not Equal operator <> is the opposite to the equality operator =. Of course, you can get the result of a Not Equal operation by preceding an equal expression with the NOT operator.
Practical Learning: Comparing For Inequality
Less Than <
To find out whether one value is lower than another, use the < operator. The formula to apply is:
value1 < value2
The value held by Value1 is compared to that of Value2. The operation can be illustrated as follows:
Practical Learning: Comparing For Lower Value
Less Than Or Equal To <=
The Equality and the Less Than operators can be combined to compare two values. This allows you to know if two values are the same or the first is lower than the second. The operator used is <=. The formula to follow is:
value1 <= value2
If both value1 and value2 are the same, the result is true or positive. If the left operand holds a value lower than the second operand, the result is still true. If the left value is strictly higher than the other Value, the comparison produces a False result.
A <= operation can be illustrated as follows:
Practical Learning: Testing Lower or Equal Value
Greater Than >
To find out if one value is strictly greater than another, you can use the > operator. The formula to apply is:
Value1 > Value2
The > operator can be illustrated as follows:
The > operator is the opposite to <=.
Practical Learning: Comparing For Greater Value
Greater Than or Equal To >=
The greater than and the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. Its formula is:
Value1 >= Value2
The operation can be illustrated as follows:
The >= operator is the opposite to <.
Practical Learning: Comparing For Greater or Equal Value
Sorting Filtered Records
Sorting Records in Regular Views
You don't have to sort records when filtering them, but you can. If want to sort the records when designing a query, in the bottom side of the Query window, use the Sort combo box of the desired field.