Running Queries Based on Multiple Criteria

You can narrow down the data Access pulls from a database table by specifying criteria in more than one field of the QBE grid. Sometimes you might want to extract records that meet all the criteria in all the fields (this AND that), and sometimes you might want to extract records that meet any criterion in any field (this OR that). If you use wildcards because you do not want to search for an exact match, Access uses the LIKE operator. In this topic, we ll look at an example that uses the AND operator. We ll also look at how to run a query that extracts information from more than one table.

Using Wildcards

When you want to select fields based on only part of their entries, you can use the? and * wildcards before, after, and between characters as placeholders for unknown or varying characters. The question mark (?) wildcard is used as a placeholder for one character. The * wildcard is used as a placeholder for any number of additional characters, including no additional characters. For example, to find all employees that live in Pinedale, you would create a query using Pine???? as the criterion. This query allows for exactly four characters to appear after Pine. But let s say you re organizing a car pool and want to find employees living in Pinedale and the neighboring Pinedale Heights. You would then create a query using Pine* as the criterion, which allows for any number of characters to appear after Pine. When you use wildcards, Access precedes the criterion with the LIKE operator to indicate that you do not want to look for an exact match.

 

Using the AND Operator

Suppose you need to know which of the Durahomes jobs is complete so that you can bill the customer; that is, you want to see records that are both for Durahomes and complete . In Access, the AND operator is implied whenever you use more than one criterion in a single row of the QBE grid. Follow these steps to extract the required record:

1. If the Jobs table isn t already selected in the Tables list, click it, and then on the Access toolbar, click Query in the New Object button s drop-down list.

2. Switch to Design view to display the Jobs box in a Select Query window.

3. Add the JobID , CustomerID , Location , and Status fields to the QBE grid, type Complete as a criterion in the Status field, and type 30 as a criterion in the CustomerID field.

   The QBE grid is shown in this graphic:

   

4. Run the query.

   Access displays a record for the completed Durahomes job:

   

5. Save the query by clicking the Save button on the toolbar and typing Complete Jobs in the Query Name text box.

Using the OR Operator

When you want to find records that meet any of two or more criteria, you use the OR operator. Enter the criterion for the first field in the Criteria row and enter all the criteria for the other fields in the or row. For example, if you wanted to throw an office party for employees whose birthdays fall in the current month or who have been with the company for five years as of the current month, you could use the Date Hired field in the Criteria row and the Date of Birth field in the or row to find records with matching criteria in either of those fields. If you want to find records that meet any of two or more criteria in the same field, you enter the criteria under each other in that column of the QBE grid. (Although only one row is designated as the or row, the OR operator is implied for all the rows below the or row.)

 

More logical operators

In Access, you can use the following operators as criteria: = (equal to); < (less than); > (greater than); <= (less than or equal to); and >= (greater than or equal to). These operators are often used for such tasks as identifying employees whose salaries fall within a certain range or locating high-volume customers.

 

Extracting Information from Multiple Tables

At times, you might want a query to pull information from more than one table. Let s consider the Complete Jobs query we just ran. In our sample database, the roofing company has only a few customers, so including just a customer ID number works fine. However, if the roofing company had many customers, we might want to include the customers names in addition to their numbers . Because the customers names are not part of the Jobs table, but are instead stored only in the Customers table, we must use both tables in a query to get the information we want. Let s try this out now:

1. With the Complete Jobs query still open , switch to Design view to display the query in the Select Query window.

2. Click the Show Table button, double-click Customers , and then close the Show Table dialog box.

   The window now contains boxes for both tables. Because you created a relationship between these tables in an earlier section, Access indicates the relationship in the Select Query window by drawing a line between the CustomerID fields in the two boxes.

3. In the Customers box, double-click the Name field to add it to the QBE grid.

4. In the QBE grid, deselect the Show box for CustomerID .

5. Run the query.

   Access displays the job number, job location, status, and customer name, as shown in this graphic:

   

6. Close the Select Query window, clicking Yes to save the query.

   All the queries you ve run so far have been select queries, which output results in datasheets . Unless you save this type of query, the information in the datasheet is temporary; it goes away when you close the Select Query window. In the next topic, we ll explore action queries, which you can use to modify the information in your tables and even to create new tables.