Finding, Filtering, Sorting  «Prev  Next»

Lesson 4Filter records to display a subset of records in a table
Objective Filter records to display a subset of records in a table.

Display Subset of Records

Filter Records to display Subset of Records in Table

Some of the analysis you may want to do with your data may require that you find records that match a certain criteria. Much of this type of analysis is done using queries. Queries allow you to ask questions of your data such as “ Which of my clients who buy over $1000 of services a month are based in Arizona?” This question requires using data from multiple tables and grouping the data into months. However, if you had a question like “Which of my clients are in Arizona,” which can be answered with data in one table, you can find the answer using the filter feature provided in datasheet view.

Filter by Selection

In order to use Filter by Selection to answer your question, you must be looking for data within one table, and you must be able to find at least one record that meets your criteria.
The following SlideShow demonstrates this process.


  1. In order to find all my clients who are in Arizona, I first have to find one client in Arizona. In fact, the first client has an address in Arizona. I put the cursor in the state field for the first record - this is the value I want to match. I want to find all the addresses with AZ in the state field. Then I click the Filter by selection button.
  2. The datasheet now display only clients in Arizona.
  3. Notice that the Status bar at the bottom of the datasheet now reads Record 1 of 7 (Filtered). This tells you that not all records in the table are being displayed.

Filter Records To Display Record Subsets

Filter For option

If you prefer, you can type in a value rather than finding a record with the value you want to match. Here is how: right-click the field that you want Access to look at and choose Filter For from the shortcut menu. Then type the value into the field and press Enter.

Filter by Exclusion

You may also find it useful to be able to filter data by excluding a certain value. To filter by exclusion, put the cursor in a cell with the value that you want to exclude and select Records>>Filter>>Filter Excluding Selection. Remove this filter by clicking the Remove Filter button.
Learn how to filter records using multiple criteria in the next lesson.