Objective 9. Use Compound Criteria

Table of contents:

You can specify more than one conditioncriteriain a query; this is called compound criteria. Compound criteria enable you to create queries that are quite specific. Two types of compound criteria used in queries are AND and OR, which are logical operators. Logical operators allow you to enter criteria for the same field or different fields.

Compound criteria that create an AND condition will return those records in the query result that meet both parts of the specified criteria.

Activity 2.16. Using AND in a Query

In this activity, you will specify the criteria in the query to display pledges obtained by the Culinary Arts Club for amounts greater than $20.

1.	With Queries selected on the Objects bar, double-click Create query in Design view. Add the Pledges table to the table area, and then Close the Show Table dialog box.
2.	Expand the table area and the field list. From the Pledges field list, add the following fields to the design grid: Club Affiliation, Pledge Amount, and Date Collected.

3.	In the Criteria row, under Club Affiliation, type Culinary Arts Club and then press . In the Criteria row under Pledge Amount, type >20 and then compare your screen with Figure 2.32. Figure 2.32. (This item is displayed on page 1002 in the print version) Placing the criteria for both fields on the same line in the Criteria row creates the AND condition. The results will display records that contain both Culinary Arts Club and a Pledge Amount greater than $20.
	[Page 1002]
4.	Click the Run button . Three records display that match both conditionsCulinary Arts Club in the Club Affiliation field and greater than $20 in the Pledge Amount field.
5.	Click the Close Window button . Click Yes to save changes to the query, and in the Save As dialog box using your own name, type 2A AND Query Firstname Lastname as the query name. Click OK or press .
6.	If you have been instructed to submit your 2A_Club_Fundraiser database file electronically, skip this step. Otherwise, in the Database window, click once to select your 2A AND Query and then, on the Database toolbar, click the Print button . You can print any object from the Database windowthe object does not have to be displayed on your screen to print.

Activity 2.17. Using OR in a Query

Use the OR condition to specify multiple criteria for a single field, or multiple criteria on different fields when you want the records that meet either condition to display in the results.

1.	Double-click Create query in Design view. Add the Pledges table to the table area and expand the table area and the field list. Add the following four fields to the design grid: Student#, Club Affiliation, Pledge Amount, and Date Collected.
2.	In the Criteria row, under Club Affiliation, type Student Government Association
	[Page 1003]
3.	In the design grid, locate the or row. In the or row, under Club Affiliation, type International Business Student Association and then press . Widen the column so that you can check your typing. Compare your screen with Figure 2.33. Figure 2.33. Another Way: To Use the OR Compound Operator You can type multiple criteria for the same field on the Criteria row. For example, type Student Government Association or International Business Student Association. Both organizations will display in the results.
4.	Run the query. The query results display six records whose Club Affiliation is either International Business Student Association or Student Government Association. Use the OR condition to specify multiple criteria for a single field.

5.	Return to the Design view . Under Club Affiliation, in the or row, select the existing value, and then press . In the or row under Pledge Amount type >30 and then compare your screen with Figure 2.34. Figure 2.34. (This item is displayed on page 1004 in the print version) Because criteria has been entered for two different fields, Access will return the records that are Student Government Association or that have a pledge amount greater than $30.
	[Page 1004]
6.	Run the query to display the 10 records that match either condition, and then compare your screen with Figure 2.35. Figure 2.35.
7.	Close the Query window and save the query as 2A OR Query Firstname Lastname as the query name. Click OK.
8.	If you have been instructed to submit your work electronically, skip this step. Otherwise, in the Database window, select your 2A OR Query and then Print the query results. Leave the database open for the next activity.