Filtering Records

In Access 2003 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:

Performing a Filter By Selection

At times, 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 anywhere in the field where you want to filter the records in a table.

    Select Field to be Used in Filter
  • Click the Filter by Selection button in the Standard toolbar, or choose RecordsFilterFilter By Selection from the menu bar to apply the filtering.

    Filter by Selection Option in the Records Menu
  • The filter produces a display that shows only those records that match the filter's definition (e.g., North Carolina). The status area reflects only the filtered records.

    Resulting Display of Filtered Records

Removing a filter

To remove a filter:

  • Click the Remove Filter button on the Standard toolbar, or choose RecordsRemove Filter/Sort from the menu bar.

    Remove Filter/Sort option in the Records Menu
  • The records revert to their ordering before the sort was applied.

    Sort Order When Filter Removed
  • If you want to reapply the filter, click the Apply Filter button (this button acts like a toggle to turn the filter on and then turn the filter off).

Saving a filter

Access defaults to displaying all records in a table. Filters are not initially applied to a table. Filtering table records actually change the table design. When you attempt to close a table after a filter, Access will prompt you to save the changes to the table design.

To save a filter:

  • Exit the table.
  • Click the "Yes" button in response to the question, Do you want to save changes to the table?

    Save Changes Confirmation

    The filter order is saved.

When you open the table or form later, all records will be visible. Click the Apply Filter button to reapply the filter. However, Access saves only the last filter you create.

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

To cancel a filter:

  • Exit the table.
  • Click the "No" button in response to the question, Do you want to save changes to the table?

    The change is not saved; the table remains in its original design.

Performing a Filter Excluding Selection

The Filter Excluding Selection works in the opposite manner as the Filter by Selection. Instead of specifying the filter to be used to view records (e.g., everyone in North Carolina), Filter Excluding Selection allows you to view data that does not include the specified criterion (e.g., everyone NOT in North Carolina).

To apply Filter Excluding Selection:

  • Click anywhere in the field that is to be excluded from the filter.

    Select Field To Be Used in Filter
  • Choose RecordFilter Excluding Selection from the menu bar, or right-click and choose Filter Excluding Selection from the shortcut menu.

    Filter Excluding Selection option under the Reports Menu
  • All records except the criterion you excluded are now visible.

    Filtered Records
  • The status area shows only the filtered records displayed on the screen.

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

Performing a Filter By Form

The Filter by Form feature is used when you are working in Form view and not in Datasheet view. Filter by Form works the same way as the Filter by Selection method, except the filtering is defined on a blank contact data entry form as if you were creating a new contact record.

To Filter By Form:

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

    Blank Record Entry
  • Click the Filter By Form button in the Standard toolbar.
  • When you click in a field (e.g., State/Province), a drop-down list is opened and displays all criteria (filter values) available for selection.

    State/Province drop-down List
  • Select a filter value from the drop-down list (such as North Carolina to view all records in North Carolina).
  • To display records meeting more than one search criteria, click the Or tab located at the bottom of the screen. Otherwise, continue to the next step.

    Or Tab Used in the Filtering Process
  • Click the Apply Filter button to apply the filter and view the filtered records.

    View of Filtered Records

Challenge!

  • Open the Content Management database.
  • Open the Contacts table in Datasheet view.
  • Filter by Selection on any record that reveals a City of "Apex".
  • Remove the filter.
  • Perform a Filter Excluding Selection on any record that reveals a City of "Raleigh".
  • Remove the filter.
  • Filter By Form on the City of "Raleigh".
  • Remove the filter.
  • Close the Contacts table, and if displayed click the "No" button in response to the Do you want to save changes to the design of table 'Contacts' system prompt.