MS Access —

Developing AND and OR Operators

Creating an AND statement.
Creating an OR statement.
The results of the AND query.
The results of the OR query.

The longer you work with Access, the more you will want to analyze your data. Before long you will want to create queries that match two or more conditions, such as "Which people have bought our products AND live in Michigan?" You might also want to create a query that matches only one of several conditions, such as "Which people have bought our beach balls OR water rafts?"

Toward that goal, this lesson introduces AND and OR operators. Here's the rundown on the two:

  • AND narrows your query, making it more restrictive. For example, you could filter for employees who are from Washington AND who have been with the company for more than five years. To create an AND query, enter the criteria for the fields on the same Criteria row of the design grid.

  • OR relaxes your query, so that more records match. For example, you could filter for employees who are from California OR Minnesota. To create an OR query, enter the criteria for the fields on different Criteria rows of the design grid.

The terms AND and OR operators may sound like they belong to the frighteningly technical world of programming, but if you already have a basic understanding of queries, they are remarkably easy to use.

  1. Open the Customers List query in Design view.

    Remember: To open any database object in Design view, simply select the object and click the Design button in the Database window.

    The Customers List query appears in Design view. For this exercise you want to find which of your customers are from France AND own their own business. You will need to create an AND query because the ContactTitle field must equal "Owner" and the Country field must equal "France." To create an AND query, simply list each criteria on the same line, as shown in figure.

  2. Click the ContactTitle column's Criteria row and type Owner.

    This will retrieve records whose ContactTitle equals "Owner." Next you have to enter the country criteria.

  3. Click the Country column's Criteria row and type France.

    Because you entered the Country criteria in the same Criteria row as the ContactTitle criteria, Access will treat it as an AND statement. (Which of my customers is an owner AND is from France?)

    Move on to the next step to view the results of your first AND query.

  4. Click the View button on the toolbar.

    Access displays the results of the query, as shown in figure. Notice that the results match your queries' AND criteria all the records have "Owner" in the ContactTitle field and "France" in the Country field.

    Next we'll modify the query and create an OR statement.

  5. Switch to Design view by clicking the View button on the toolbar.

    You're back in Design view. First let's remove the Owner criteria from the query.

  6. Delete the "Owner" criteria from the ContactTitle Criteria row.

    This time you want to find which of your customers are from France OR are from Mexico. You will need to create an OR query to find these records. To create an OR query, simply list each criteria on its own line, as shown in figure.

  7. In the Country column, click the second Criteria row and type Mexico.

    Because you entered each criteria in a different row, Access will treat it as an OR statement. (Which customers are from France OR are from Mexico?)

    Move on to the next step to view the results of your OR query.

  8. Click the View button on the toolbar.

    Access displays the results of the query, as shown in figure. Notice that the results match your queries' OR criteriaall the records have either "France" OR "Mexico" in the Country field.

  9. Click the Save button on the toolbar to save your work and close the query window.

TO CREATE AND/OR CRITERIA:

  1. OPEN/DISPLAY THE QUERY IN DESIGN VIEW.

  2. ENTER YOUR CRITERIA IN THE APPROPRIATE FIELD'S FIRST CRITERIA BOX.

  3. ENTER ADDITIONAL CRITERIA AS FOLLOWS:

    AND: ENTER ADDITIONAL CRITERIA FOR ONE OR MORE FIELDS IN THE APPROPRIATE FIELD'S CRITERIA BOX. ALL AND CRITERIA SHOULD APPEAR ON THE SAME ROW.

    OR: ENTER ADDITIONAL CRITERIA FOR ONE OR MORE FIELDS IN THE APPROPRIATE FIELD'S CRITERIA BOX, USING A DIFFERENT ROW FOR EACH OR CRITERIA.