Filtering Records

In Access filter data so you see only what you want to see. This will help you filter out the information in your Access database that you don't need.

Introduction

Access 2007 Another useful way to look at—or analyze—the data in a database is by filtering it. Filtering groups your data together based on one or more criteria for a given field, then displays only the records that contain those specified values and criteria. Filtering data is easy with Access 2007. In this lesson, we'll show you how to filter records using common filter commands. You'll also learn how to use the toggle button to switch between filtered and unfiltered results, as well as how to perform advanced filters and save the filtered results.

Filtering records

Filtering records

When you tell Access 2007 to filter your records, you are asking it to:

You can filter data using what Microsoft calls the Filter by Field and Filter by Selection methods. Additionally, Access 2007 can perform an Advanced Filter, which is essentially a small query that is run on only one table. These options are described in the table below.

MethodDescription
Filter by FieldLists all of the values that have been entered in a specified field so you can choose which value or values you want Access to find
Filter by SelectionOffers the ability to search records based on the value that is currently selected in a table; you can ask it to find records that:
  • Equal the value
  • Do not equal the value
  • Contain the value
  • Do not contain the value
Advanced FilterNarrows filtered results further by performing additional filters or sorts

Filtering by field

When you Filter by Field, Access 2007 finds all of the values that have been entered in the specified field. Access lists all of these values for you so you can choose which value or values you want Access to find for you within that field. Once you choose which value to filter by, Access returns the results to you as a subset of records in the table.

To use the Filter by Field feature:

Filter by FieldResults for Filter by State for NC Customers

The results will appear in the table. In the example above, the results are showing all records for customers living in North Carolina (NC). Notice that all other records are hidden from view but that the table name is still Customers.

To toggle between filtered and unfiltered results:

As with most actions in Access, you can toggle between your filtered results and unfiltered tables in a variety of ways:

Filtering by selection

When you Filter by Selection, Access 2007 returns the results to you as a subset of records in the table. It assumes that your current selection is what you want to use as your filter value, or criteria.

To use the Filter by Selection feature:

Filter by SelectionFiltering by Selection of Raleigh

Sorting or filtering the filtered results

Access 2007 lets you narrow the filtered results further by applying another level of sorting or filtering. To do this:

Another way to do this is to use the Advanced Filter command.

Using advanced filters

An Advanced Filter is similar to a multilevel sort in Microsoft Excel. An Advanced Filter can help you further narrow your records. This is like running a miniature query on only one table. It uses a screen that's similar to the query design screen, and it can even save your results as a query so you can use them again later.

To apply an Advanced Filter:

Important: Access will perform the sorts left to right, according to the way they are displayed on the Advanced Filter design screen. Once all of the sorts are complete, Access will perform filter functions. Similar to sorts, Access filters from left to right based on the order displayed on the Advanced Filter design screen.

To view the results, click the Toggle Filter button on the Ribbon.

Your filtered results will appear in a new table. You can save the advanced filter so you can run it again later if you want.

To save the Advanced Filter:

Access 2007 saves Advanced Filters as queries because they are simple queries run on only one table.

Challenge!

If you haven't already done so, save the sample Ready2Read database to your computer.

  • Use the Filter by Field method to filter the Customers table to find all customers from NC.
  • Use the Filter by Selection method to filter the Customers table, looking for all NC customers NOT in Raleigh.
    Hint: Use the Does Not Equal option.
  • Practice toggling between the filtered results and the unfiltered tables.
  • Apply an Advanced Filter on the Books table to find all of the Kids books, sorted by price with the highest price first.
  • Save your Advanced Filter with whatever name you choose.