Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

211

FIGURE 5.18

A simple rearrangement in the QBE grid results in a very different query.

The difference is significant because the rearrangement is considerably more restrictive when

returning records. Only one record is returned by qryFigure_5-18, while qryFigure5-17

returns 17 rows.

Using And and Or together in different fields

After you’ve worked with And and Or separately, you’re ready to create a query using And and Or

in different fields. In the next example, the query displays records for all buyers of motorcycle

models in Connecticut and buyers of truck models in New York:

1. Use the query from the previous example, emptying the two criteria cells first.


2. Enter CT in the Criteria row in the State column.


3. Enter NY in the or row under CT in QBE grid.


4. Type Motorcycles as criteria in the Category field.


5. Enter Trucks under Motorcycles in the Category field.

Figure 5.19 shows how the query should look. Notice that CT and Motorcycle are in
the same row; NY and Trucks are in another row. This query represents two Ands
across fields, with an Or in each field.

The important thing to notice about this query is that Access returns, essentially, two sets of data:

motorcycle model owners in Connecticut and truck model owners in New York. All other custom-

ers and model combinations are ignored.