Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

191

You learn how to create this type of complex query in the “Entering Criteria in Multiple Fields”

section, later in this chapter.

Tip
In the QBE pane, enter And criteria in the same row and Or criteria in different rows.

Access takes your graphical query and creates a single SQL SELECT statement to actually extract

the information from your tables. Click the drop-down in the ribbon’s View group and select SQL

View to change the window’s contents to display the SQL SELECT statement (shown in Figure

5.4), which Access creates from the fields and criteria placed in the QBE pane in Figure 5.3.

FIGURE 5.4

The SQL view for the query in Figure 5.3. Notice that it contains a single OR and two AND operators (in the
WHERE clause).

The SQL statement in Figure 5.4 has been slightly rearranged by the author for clarification pur-

poses. When you switch to SQL View in your database, you’ll see one long multi-line statement

with no breaks between sections.

An expression for this query’s criteria is

(tblCustomers.State = “CT” AND tblProducts.Category <> “Cars”) OR

(tblCustomers.State = “MA” AND tblProducts.Category <> “Cars”)

You must enter the category criteria (<> “Cars”) for each state in the QBE pane, as shown in

Figure 5.3. In the “Entering Criteria in Multiple Fields” section, later in this chapter, you learn to

use the And/Or operators in a Criteria cell of the query, which eliminates the redundant entry of

these fields.

Tip
In this example, you looked for all models that didn’t contain cars in the Category field. To find records that
do match a value, drop the <> operator with the value. For example, enter Cars to find all records with Cars as
the category. You don’t have to use the equal sign in the QBE pane when working with select queries.