Sorting the information in a table organizes it in a logical manner, but you still have the entire table to deal with. To locate only the records containing (or not containing) specific information, filtering is more effective than sorting. For example, you could quickly create a filter to locate only customers who live in Seattle, only items that were purchased on January 13, or only orders that were not shipped by standard mail.
You can apply simple filters while viewing information in a table or form. To filter information by multiple criteria, you can apply additional filters to the results of the first one.
In this exercise, you will filter records by a single criterion and then by multiple criteria.
Tip
The Filter commands you will use in this exercise are available in the Sort & Filter group on the Home tab, on the column menu displayed when you click a column header arrow, and on the shortcut menu displayed when you right-click a column. However, not all Filter commands are available in each of these places.
Note
USE the 02_FilterTable database. This practice file is located in the Chapter11 subfolder under SBS_Office2007.
OPEN the 02_FilterTable database, and then open the Customers table in Datasheet view.
In the City field, click any instance of Vancouver.
On the Home tab, in the Sort & Filter group, click the Selection button, and then in the list, click Equals "Vancouver".
The number of customers displayed in the table (and on the status bar at the bottom of the table) changes from 110 to 6, because only six customers live in Vancouver.
Access displays a small filter icon at the right side of the City column header to indicate that the table is filtered by that field. The Toggle Filter button in the Sort & Filter group and the Filter status on the status bar changes to Filtered.
In the Sort & Filter group, click the Toggle Filter button.
Access removes the filter and displays all the records. If you click the Toggle Filter button again, the last filter used will be reapplied.
Suppose that you want a list of all customers with postal codes starting with 880.
Click the PostalCode column header arrow, point to Text Filters, and then click Begins With.
Tip
The sort and filter options displayed when you click the column header arrow are determined by the field type. If this were a numeric field, then the submenu would be Number Filters and different options would be listed. U.S. Zip Codes and international postal codes are usually text fields to allow for the ZIP+4 codes.
The Custom Filter dialog box opens.
In the PostalCode begins with box, type 880. Then click OK.
The filtered table includes 30 records that have postal codes starting with 880.
In the Sort & Filter group, click the Toggle Filter button to remove the filter and display all the records.
Suppose you want to display only those customers who live outside of the United States.
In the Country column, right-click any instance of USA, and then click Does Not Equal "USA".
Access displays all the customers from countries other than the United States (in this case, only Canada).
Remove the filter, save and close the Customers table, and then open the Orders table in Datasheet view.
In the EmployeeID field, right-click Emanuel, Michael, and then click Equals "Emanuel, Michael".
In the OrderDate field, right-click 2/1/2007, and then click On or After 2/1/2007.
Tip
To see a list of the available options for date filters, right-click any cell in the OrderDate column and then point to Date Filters.
You now have a list of orders placed with the selected employee on or after the specified date. You could continue to refine the list by filtering on another field, or you could sort the results by a field.
