Filtering Data

Although sorting rearranges the records in the table, you might need to see a subset of the records in a table based on a particular criterion. Filtering is used for this purpose. The Filter feature temporarily hides records from the table that do not meet the filter criteria.

For example, you might want to view only the records in an employee table for the employees who have exceeded their sales goal for the year. Or in an order table, you might want to find orders that were placed on a particular date. Filters can help you temporarily narrow down the records shown in the table based on your criteria.

You can apply a filter in three ways: Filter by Selection (or Filter Excluding Selection), Filter by Form, and Advanced Filter/ Sort . The first two methods are very easy ways to quickly filter the records in a table.

The Advanced Filter/Sort feature uses a Design view that is almost the same as the Query Design view (covered in Lesson 2). If you learn how to create queries (which are really nothing more than advanced filters/sorts), you will be able to work with the Advanced Filter/Sort feature.

This section covers Filter by Selection and Filter by Form. Next, take a look at how you filter by selection.

Filter by Selection

Filtering by selection is the easiest method of filtering, but before you can use it, you must locate a field that contains the value that you want to use to filter the table.

To filter by selection, follow these steps:

1. Locate a field in a record that contains the value you want to use to filter the table. For example, if you want to see all the customers in Germany, you would find a field in the Country field column that contains the text, "Germany."

2. Click in the field that contains the value you will use as the filter.

3. Click the Filter by Selection button on the toolbar, or select Records , point at Filter , and then choose Filter by Selection . The records that match the criteria you selected appear, as shown in Figure 14.3.

   Figure 14.3. The table will be filtered by the field data you selected.

   

Fine-Tuning Filter by Selection You can also filter the table by selecting only a portion of an entry in a field. For example, if you want to filter the records by last names beginning with the letter S, select the S in a last name that appears in the Last Name field in a record.

With Filter by Selection, you can filter by only one criterion at a time. However, you can apply successive filters after the first one to further narrow the list of matching records.

You can also filter for records that don't contain the selected value. Follow the same steps as outlined in this section, but choose Records, point at Filter, and choose Filter Excluding Selection in step 3.

After you have finished viewing the records that match your filter criteria, you will want to bring all the table records back on screen. Select Records, Remove Filter/Sort.

Filter by Form

Filtering by form is a more powerful filtering method than filtering by selection. With Filter by Form, you can filter by more than one criterion at a time. To filter by form, follow these steps:

1. With the table open in the Datasheet view, click the Filter by Form button on the toolbar, or select Records , point at Filter , and then select Filter by Form . A blank form appears, resembling an empty datasheet with a single record line.

2. Click in the field for which you want to set a criterion. A down arrow appears for a drop-down list. Click the arrow and select the value you want from the list (see Figure 14.4). You also can type the value directly into the field if you prefer.

   Figure 14.4. Set the criteria for the filter using the drop-down list in each field.

   

3. Enter additional criteria for the filter as needed using the drop-down lists provided by the other fields in the table.

4. After you enter your criteria, click the Apply Filter button on the toolbar. Your filtered data appears in the Table window.

As in Filter by Selection, you can remove a filter by clicking the Remove Filter button (same icon as for Apply Filter) or by selecting Records, Remove Filter/Sort.

Saving Your Filter As a Query

If you design a filter that you would like to keep, it resides on the Query list in the database window. You will work with queries in Lessons 15 and 16.

To save a filter as a query, follow these steps:

1. Display the filter in Filter by Form view.

2. Select File , Save As Query . Access asks for the name of the new query.

3. Type a name and click OK . Access saves the filter as a query.