Filtering Data


10 Minute Guide to Microsoft Access 2002
By Joe Habraken
Table of Contents
Lesson 16.  Sorting, Filtering, and Indexing Data

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 tempo rarily hides records from the table that do not meet the filter criteria.

For example, you might want to view the records in an employee table where the employees 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 Ex cluding 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 18, "Creating Queries from Scratch"). 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. For example, if you want to filter a customer table by a country, such as Germany, you must locate a field in a record that contains the text "Germany."

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 "Germany."

  2. Select the data in the field.

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


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.

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

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 16.4). You also can type the value directly into the field if you prefer.

    Figure 16.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 or by selecting Records, Remove Filter/Sort.

Saving Your Filter As a Query

If you design a filter that you would like to keep, you can save it as a query. After it is saved as a query, it resides on the Query list in the database window. You will work with queries in Lessons 17 and 18.

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.


10 Minute Guide to Microsoft Access 2002
10 Minute Guide to Microsoft Access 2002
ISBN: 0789726319
EAN: 2147483647
Year: 2000
Pages: 160
Authors: Joe Habraken © 2008-2017.
If you may any questions please contact us: