- What Is a Query and When Should You Use One?
- Opening a Query in Datasheet View
- Opening a Query in Design View
- Running a Query
- Adding and Removing Fields
- Modifying the Sort Order of a Query
- Working with Simple Criteria
- Modifying the Datasheet View of a Query
- Saving a Query
- Printing Query Results
- Closing a Query
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Simple Criteria
You can limit the records that you see in the result of a query by adding criteria to the query. For example, you might want to see just the customers in California, or you might want to view just the orders with sales over $500. You could also view sales that occurred within a specific date range. By using criteria, you can easily accomplish any of these tasks, and many, many more.
Using an Exact Match Query
An exact match query locates data only when there is an exact match with the criteria that you enter. Here's how you run an exact match query:
Open the desired query in Design view.
Select the cell on the Criteria row below the field for which you want to add the condition.
-
Type the criteria you want to apply for that field. For example, type Sales Representative in the Title field (see Figure 3.11).
-
Click the Run button. The results of this query are shown in Figure 3.12.
){kind=small}
Figure
3.11 Entering simple criteria.
Although Access is not case-sensitive, and you can therefore enter criteria in either upper- or lowercase, the criteria you enter must follow specific rules. These rules vary depending on the type of field the criteria apply to (see Table 3.1).
){kind=small}
Figure
3.12 Records with Sales Representative in the Title field.
Table 3.1 Rules for Criteria, Based on Type of Field
|
Type of Field |
Description |
|
Text |
After you type the text, Access puts quotes around the text entered. |
|
Number/Currency |
-You type the digits, without commas or dollar signs but with decimals, if applicable. |
|
Date/Time |
You enter any date or time format. |
|
Counter |
You type the digits. |
|
Yes/No |
For a yes, you type yes or true. For no, you type no or false. |
Creating Criteria Based on Multiple Conditions
There may be times when you want to create a query that contains two or more conditions. You would do this, for example, if you only wanted records in the state of California that had sales within a certain date range to appear in the output. The And condition is used to indicate that both of two conditions must be met in order for the row to be included in the resulting recordset. You can use the And condition in the same field or on multiple fields.
Using the And Condition on Multiple Fields
By placing criteria for multiple fields on the same line of the query grid, you create an And condition. This means that both conditions must be true in order for the records to appear in the result. An example of an And condition on two fields would be State Field = 'TX' And Credit limit >=5000. Here's how you create an And condition:
Open the desired query in Design view.
Select the cell on the Criteria row below the field that contains the first condition you want to enter.
Type the first criterion you want to enter. For example, you can type Sales Manager as the criterion for Contact Title.
Select the cell on the Criteria row below the field that contains the second condition you want to apply.
-
Type the second criterion you want to apply. Figure 3.13 shows USA as the criterion for Country.
-
Click the Run button to run the query. Only rows that meet both conditions appear in the query result (see Figure 3.14).
){kind=small}
Figure
3.13 The design of a query with criteria for Contact Title and
State.
){kind=small}
Figure
3.14 A result that includes all sales managers in the United States.
Using the And Condition in a Single Field
There are only a few situations in which you would use an And condition in a single field. This is because in most situations, using the And condition in a single field would yield a recordset with no results. For example, the criteria State = TX And State = CA would yield no results because the state cannot be equal to both values at the same time. On the other hand, HireDate > 7/1/2001 And HireDate < 6/30/2002 would return all employees hired in that date range. Here's how you would enter this sort of criteria:
Open the desired query in Design view.
Select the cell on the Criteria row below the field that contains the condition you want to add.
Type the first criterion you want to add (for example, HireDate > 7/1/2001).
Type the keyword And.
Type the second criterion (for example, HireDate < 6/30/2002).
Click the Run button. Access runs the query.
CAUTION
You need to make sure when you are adding the criteria to each field that you remain on the same row of the query grid.
Using Wildcards in a Query
You can use wildcards to select records that follow a pattern. However, you can use the wildcard characters only in Text or Date/Time fields. You use the * to substitute for multiple characters and the ? to substitute for single characters. To practice using wildcards in a query, follow these steps:
-
Open the desired query in Design view.
-
Select the cell on the Criteria row below the field that contains the condition.
-
Type the criteria, using a wildcard in the desired expression. In Figure 3.15 the expression Like Sales* is entered for the Contact Title field. This expression returns all rows where the Contact Title begins with Sales.
-
Click the Run button. The result of the query, shown in Figure 3.16, indicates all the records where Contact Title begins with Sales.
){kind=small}
Figure
3.15 An example that contains Sales* as the criterion for Contact
Title.
Table 3.2 provides examples of how to use wildcards.
){kind=small}
Figure
3.16 The result of running a query with criterion that contains the wildcard
*.
Table 3.2 Examples of Using Wildcards
|
Expression |
Results |
|
Sm?th |
Finds Smith or Smyth. |
|
L*ng |
Finds any record that starts with L and ends in ng. |
|
*th |
Finds any record that ends in th (for example, 158th or Garth). |
|
*on* |
Finds any record that has on anywhere in the field. |
|
*/2000 |
Finds all dates in 2000. |
|
6/*/2000 |
Finds all dates in June 2000. |
NOTE
Access displays the word Like in the criteria cell before a wildcard criterion. It is not necessary to type the word Like in the criterion cell before the criterion.
Using Comparison Operators in a Query
Sometimes you want to select records in a table that fall within a range of values. You can use comparison operators (=, <, >, <=, and >=) to create criteria based on the comparison of the value contained in a field to a value that you specify in your criteria. Each record is evaluated, and only records that meet the condition are included in the recordset. To practice using comparison operators in queries, follow these steps:
Open the desired query in Design view.
Select the cell on the Criteria row below the field for which you want to apply the condition.
Type a comparison operator and the criterion you want the query to apply (for example, >100).
Click the Run button. The result of the query appears, in Datasheet view.
Table 3.3 gives an example of comparison operators used for a field called Sales. It shows the operators, provides an example of each, and discusses the records that Access would include in the output.
Table 3.3 Comparison Operators Used to Compare Against a Field Called Sales
|
Operator |
Indicates |
Example |
Includes Records Where |
|
> |
Greater than |
>7500 |
sales are over 7500 |
|
>= |
Greater than or equal to |
>=7500 |
sales are 7500 or more |
|
< |
Less than |
<7500 |
sales are under 7500 |
|
<= |
Less than or equal to |
<=7500 |
sales are 7500 or less |
|
<> |
Does not equal |
<>7500 |
sales are not 7500 |
|
Between |
Range of values |
Between 5000 And 7500 |
sales are between 5000 and 7500 |
NOTE
You can use the word Not in place of the <> symbols.
Using the Or Condition on a Single Field
The Or condition states that either condition of two conditions should be met in order for the record to appear in the result set. You can use the Or condition on a single field or on more than one field. To practice using an Or condition on a single field, follow these steps:
Open the desired query in Design view.
Select the cell on the Criteria row below the field that contains the condition.
Type the first criterion you want the query to apply. For example, you could type Sales Manager as a criterion for the Contact Title field.
Select the cell below the current cell (this is the Or row).
-
Type the second criterion you want the query to apply. For example, you could type Sales Agent as the criterion for the Contact Title field (see Figure 3.17).
-
Click the Run button. The result of this query is shown in Figure 3.18. Notice that the result contains all the sales managers, sales agents, sales representatives, and owners.
)
Figure
3.17 Using an Or condition on the Contact Title field.
){kind=small}
Figure
3.18 The result of a query that contains all the records that contain Sales
Manager, Sales Agent, Sales Representative, or Owner
in the Contact Title field.
Using the Or Condition on Multiple Fields
An alternative to using the Or condition on a single field is to use the Or condition to create criteria on multiple fields. An example would be City equals London or Contact Title equals Sales Agent. These criteria would return all companies in London, regardless of the contact title, and all sales agents, regardless of the city. Here's how you use the Or condition on multiple fields:
Open the desired query in Design view.
Select the cell on the Criteria row below the field for which you want to apply the first condition.
Type the first criterion you want the query to apply (from the criterion mentioned in the introduction to these steps).
Select the cell in the Or row below the second field for which you want to apply the criterion.
-
Type the second criterion you want the query to apply (see Figure 3.19).
-
Click the Run button. The result of this query is shown in Figure 3.20. Notice that the output contains all rows where City is London or Contact Title is Sales Agent.
)
Figure
3.19 Using the Or condition on multiple fields.
)
Figure
3.20 The result of a query that contains the rows where City is
London or Contact Title is Sales Agent.
NOTE
When you use two fields in an Or condition, you need to make sure the criteria are listed on two separate lines. If you don't, they will combine as an And condition.
You need to use the Or condition to find dates or numbers that fall outside a range (for example, before 6/1/96 or after 1/1/97).
You can use the word In and list the multiple criteria, separated by commas, in parentheses (for example, In ('USA', 'France', 'Canada')).