Filtering Records

In Access 2000 filtering records may seem confusing at first. This free lesson offers step-by-step instructions.

Introduction

By the end of this lesson, you should be able to:

Filtering records

Sometiomes you might want to view only those records that match a specific criterion. A filter is a technique that lets you view and work with a subset of data. Applying a filter to an Access table, form, or query temporarily hides records that don't meet your search criteria. For example, you may only want to work with data pertaining to a specific zip code.

To filter by selection:

  • Click in the field that contains the type of data you're looking for. For example, if you are working with the Great Lake Elementary School database's Students table in Datasheet view and want to see a list of all students who live in some city, go to the City column and click in a field that contains Some City.

Click in the field that contains the type of data you're looking for

  • Click the Filter By Selection button.
    Filter by Selection button
  • The type of data you filtered will be visible. For example, only the students who live in Some City will be visible.
  • The status area shows only filtered records.

Partial view of Filter By Selection results

Removing filters

To remove a filter:

  • Click the Remove Filter button.
  • If you want to reapply the filter, click the Apply Filter button (hover your mouse pointer over the button to observe the tool tip).

Apply/Remove filter button

Important PointYou can apply filters to filtered data to narrow your search even further.

If you have created a filter that you know you'll reuse at a later date:

  • Apply the filter just before closing the object.
  • Access asks if you want to save changes to the design.
  • Click Yes to automatically save the filter.
  • When you reopen the table or form, all records will be visible. Click the Apply Filter button to reapply the filter. However, Access only saves the last filter you create.

Filtering excluding selection

If you want to see all data in a form or table except for a certain criterion, use Filter Excluding Selection.

To apply Filter Excluding Selection:

  • Locate a record with the criterion you want to exclude. For example, you may want to exclude a particular zip code.
  • Choose Recordlesson actionFilter Excluding Selection or right-click and choose Filter Excluding Selection from the shortcut menu.
  • All records except the criterion you excluded are now visible.
  • The status area shows only filtered records.

Important PointRemove this filter by clicking the Remove/Apply Filter button.

Important PointAll sorting and filtering commands are available in the Records menu (on the menu bar) and on the shortcut menu.

Picure of Records menu: In the records menu, all filtering and sorting commands are available

Filtering by form

The Filter by Form feature works the same way as the Filter by Selection method, except you set up your search values on a blank form or datasheet.

To Filter by Form:

  • In Form or Datasheet view, click the New Record button. This creates a blank form or datasheet.

New Records button

  • Click the Filter By Form button.

Filter by Form button

  • When you click in a field, a drop-down list containing the criteria in the field (filter values) appears.

Picture of Great Lake Elementary form - When you click in a field, a drop-down list containing all the criteria in the field

  • Select a filter value from the drop-down list in one or more fields.

Picture of Great Lake Elementary form - Select a filter value

  • To display records meeting more than one search criteria, click the Or tab.

Or tab

  • Click the Apply Filter button to view the filtered records.

Remove/Apply Filter button

Challenge!

  • Open the Great Lake Elementary School database.
  • Double-click the Students table. The Students table opens in Datasheet view.
  • Spend some time familiarizing yourself with the data in the table, and experiment using the skills learned in this lesson:
    • Perform a Filter By Selection.
    • Remove the filter.
    • Perform a Filter Excluding Selection.
    • Perform a Filter By Form.
  • Close the table.
  • Do not save changes.