Locating Information That Matches Multiple Criteria


The filtering methods discussed earlier in this chapter are quick and easy ways to narrow down the amount of information displayed, as long as your filter criteria are fairly simple. But suppose you need to locate something more complex, such as all the orders shipped to Midwest states between specific dates by either of two shippers. When you need to search a single table for records that meet multiple criteria, or with criteria based on complex expressions as criteria, you can use the Advanced Filter/Sort command, available from the Advanced Filter Options list.

You work with the Advanced Filter/Sort command in the design grid.

In this exercise, you will filter a table to display customers located in two states. Then you will experiment with the design grid to better understand its filtering capabilities.

USE the 04_MultipleCriteria database. This practice file is located in the Chapter06 subfolder under SBS_Access2007.

OPEN the 04_MultipleCriteria database. Then open the Customers table in Datasheet view.


1.

On the Home tab, in the Sort & Filter group, click the Advanced Filter Options button, and then in the list, click Advanced Filter/Sort.

The CustomersFilter1 query window opens, displaying the Customers field list in a floating window at the top, and the design grid at the bottom.

2.

In the Customers field list, double-click LastName to copy it to the Field cell in the first column of the design grid.

3.

In the Criteria cell under LastName, type s*, and then press .

Access changes the criterion to Like "s*".

4.

In the Customers field list, double-click Region to copy it to the next available column of the design grid.

5.

In the Criteria cell under Region, type ca or wa, and then press .

Your entry changes to "ca" Or "wa". The query will now filter the table to display only customers with last names beginning with the letter S who live in California or Washington.

6.

In the Sort & Filter group, click the Toggle Filter button to display only records that match the criteria.

Access switches to the Customers table and displays the query results. There are six customers with last names beginning with S who live in either California or Washington.

7.

Click the CustomersFilter1 tab to switch to the filter window. In the or cell under LastName, type b*, and then press .

8.

In the Sort & Filter group, click the Toggle Filter button.

The result includes records for all customers with last names that begin with s or b, but some of the b names live in Montana and Oregon. If you look again at the design grid, you can see that the filter is formed by combining the fields in the Criteria row with the And operator, combining the fields in the "Or" row with the And operator, and then using the Or operator to combine the two rows. So the filter is searching for customers with names beginning with s who live in California or Washington, or customers with names beginning with b, regardless of where they live.

9.

Switch to the filter window, type ca or wa in the or cell under Region, press , and then apply the filter to display only customers with last names beginning with B or S located in California and Washington.

CLOSE the Customers table without saving your changes, and then close the 04_MultipleCriteria database.




MicrosoftR Office AccessT 2007 Step by Step
MicrosoftR Office AccessT 2007 Step by Step
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 127

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net