Sorting the information in a table organizes it in a logical manner, but you still have the entire table to deal with. If your goal is to locate all records containing information in one or more fields that match a particular pattern, one of the available Filter commands will satisfy your needs. For example, you could quickly create a filter to locate every customer of The Garden Company who lives in Seattle, or everyone who placed an order on January 13, or all customers who live outside of the United States.
You can apply simple filters while viewing information in a table or a form. These filters are applied to the contents of a selected field, but you can apply another filter to the results of the first one to further refine your search.
Tip
The Filter commands you will use in this exercise are available by pointing to Filter on the Records menu; by clicking buttons on the toolbar; and by looking at the shortcut menu. However, not all Filter commands are available in each of these places.
In this exercise, you will practice several methods of filtering information in a table.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSQueriesFilterDS folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
Open the Customers table in Datasheet view.
Click any instance of Sidney in the City field, and then click the Filter By Selection button.
The number of customers displayed in the table changes from 110 to 2, because only two customers live in Sidney.
Click the Remove Filter button to redisplay the rest of the customers.
What if you want a list of all customers who live anywhere that has a postal code starting with V7L? Find an example of this type of postal code in the table, select the characters V7L, and then click the Filter By Selection button again.
Only the two records with postal codes starting with V7L are now visible.
Click Remove Filter.
What if this table is enormous and you aren’t sure if it contains even one V7L? Right-click any postal code, click Filter For on the shortcut menu, type
V7L*in the cell, and press
to see the same results.The asterisk (*) is a wildcard that tells Access to search for any entry in the postal code field that starts with V7L.
To find out how many customers live outside the United States, remove the current filter, right-click the Country field in any USA record, and click Filter Excluding Selection on the shortcut menu.
You see all customers from other countries (in this case, only Canada).
To experiment with one more filtering technique, remove the filter, save and close the Customers table, and double-click Orders to open the table in Datasheet view.
To find all orders taken by Michael Emanuel on January 23, right-click Emanuel, Michael in the EmployeeID field, and click Filter By Selection on the shortcut menu.
Troubleshooting
If you do not see employee names listed in the EmployeeID field, it is because you continued with the database from the previous exercise. You must use the practice database supplied for this exercise. For instructions on installing the practice files, see "Using the Book’s CD-ROM" .
Right-click 1/23/2003 in the OrderDate field, and again click Filter By Selection on the shortcut menu.
You now have a list of Michael’s orders on the 23rd of January. You could continue to refine this list by filtering on another field, or you could sort the results by clicking in a field and then clicking one of the Sort buttons.
Remove the filters by clicking the Remove Filter button.
Save and close the Orders table.
CLOSE the GardenCo database.