Filtering Information in a Table

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.

Wildcards

When you don t know or aren t sure of a character or set of characters , you can use wildcard characters as placeholders for those unknown characters in your search criteria. The most common wildcards are listed in this table:

Character	Description	Example
*	Match any number of characters.	Lname = Co* returns Colman and Conroy
?	Match any single alphabetic character.	Fname = eri? returns Eric and Erik
#	Match any single numeric character.	ID = 1## returns any ID from 100 through 199

 

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 Documents\Microsoft Press\Office 2003 SBS\Queries\FilterDS folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

1. Open the Customers table in Datasheet view.

2. 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.

   Important  

   When you filter a table, the records that don t match the filter aren t removed from the table; they are simply not displayed.

3. Click the Remove Filter button to redisplay the rest of the customers.

4. 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.

5. Click Remove Filter .

6. 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 [ENTER] 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 .

7. 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).

8. 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.

9. 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 on page XXX.

10. Right-click 1/23/2003 in the Order Date 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.

    Tip  

    After you have located just the information you want and have organized it appropriately, you can display the results in a form or report. Click the New Object button on the toolbar, and follow the directions.

11. Remove the filters by clicking the Remove Filter button.

12. Save and close the Orders table.

CLOSE the GardenCo database.

Tip  

You can use the Filter commands to filter the information in a table when you are viewing it in a form. The Filter For command is often useful with forms because you don t have to be able to see the desired selection.