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

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

You 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 RecordFilter 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.

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

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

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.

• Click the Filter By Form button.

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

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

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

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

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.