Microsoft Office Tutorials and References
In Depth Information
Exhibit 4.19 Search for Audi and Toyota
Let us now consider the use of criteria to create complex queries. Each row that is
included below the titles will represent a set of conditions that will be applied to the
database. Using a single row is equivalent to using Filter . Placing a speciﬁc criterion
in a cell is similar to the selection of a particular criterion from the pull down menus
of the Filter function; leaving a cell blank permits all values to be returned. Thus,
if a row is placed in the criteria range that has no criteria entries in the ﬁelds, all
records will be returned, regardless of what is contained in the other rows.
Consider the auto sales database in Table 4.2. If we are interested in records
containing Audi for sales on or after 1/014/05, the Advanced Filter tool will return
records 10, 14, and 20. The condition on or after is set by using the “>
characters preceding the date, 1/14/05. Exhibit 4.22 shows the Advanced Filter in
action. Note how the query is executed:
1. Titles of the ﬁelds are copied and pasted to a range—A27:H27.
2. Go to the Data ribbon and select Advanced Filter .
3. A dialogue box will appear, and you identify the database—A3:H25.
4. A criteria row (or rows) is selected (A27:H28); each row implies an Or condition
(look for data records matching the criteria provided in the ﬁrst row, Or criteria
provided in second row, Or