Microsoft Office Tutorials and References
In Depth Information
To create Sarah’s query, you will combine the Not logical operator with the In
comparison operator to select customers whose City field value is not in the list ("Holland",
"Rockford","Saugatuck"). The qryHollandRockfordSaugatuckCustomers query has
the fields that Sarah needs to see in the query results. Sarah doesn’t need to keep the
qryHollandRockfordSaugatuckCustomers query, so you’ll rename and then modify the query.
To create the query using the Not logical operator:
1. Open the Navigation Pane, then in the Queries group on the Navigation Page,
right-click qryHollandRockfordSaugatuckCustomers , and then click Rename on
the shortcut menu.
2. Position the insertion point after “qry,” type Non , and then press the Enter key.
The query name is now qryNonHollandRockfordSaugatuckCustomers.
3. Open the qryNonHollandRockfordSaugatuckCustomers query in Design view,
and then close the Navigation Pane.
You need to change the existing condition in the City field to add the Not logical
4. Click the City Criteria text box, open the Zoom dialog box, click at the beginning
of the expression, type Not , and then press the spacebar . See Figure 5-7.
You can rename any
object type, including
tables, in the Navigation
Pane using the Rename
command on the
Record selection based on not matching a list of values
N ot logical operator with
list-of-values selection criterion
5. Click the OK button, and then save and run the query. The recordset displays only
those records with a City field value that is not Holland, Rockford, or Saugatuck.
The recordset includes a total of 29 customer records.
6. Scroll down the datasheet to make sure that no Holland or Rockford or Saugatuck
customers appear in your results.
Now you can close and delete the query, because Sarah does not need to run this
7. Close the query, and then open the Navigation Pane.
8. Right-click qryNonHollandRockfordSaugatuckCustomers , click Delete on the
shortcut menu, and then click the Yes button when asked to confirm the query
deletion. The query is permanently deleted from the database.
You can delete any object
type, including tables, in
the Navigation Pane using
the Delete command on
the shortcut menu.
You now are ready to answer Taylor’s question about Grand Rapids or East Grand Rapids
customers that signed contracts for less than $10,000 or that signed contracts during the winter.
Using an AutoFilter to Filter Data
Taylor wants to view the customer last and first names, company names, cities, contract
amounts, signing dates, and contract types for customers in Grand Rapids or East Grand Rap-
ids that have signed contracts for less than $10,000 or that signed contracts during the winter.
The qrySmallContractsOrWinterSignings query contains the same fields Taylor wants to view.
This query also uses the Or logical operator to select records if the ContractAmt field value is