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 “
Question: 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.
Question: How do I use the filter feature provided in datasheet view in Microsoft Access?
To use the filter feature in datasheet view in Microsoft Access, follow these steps:
  1. Open the table or query that you want to filter.
  2. Switch to datasheet view by clicking on the "Datasheet View" button on the ribbon.
  3. Click on the down arrow next to the column heading that you want to filter by. This will display a menu of filtering options.
  4. Select the desired filtering option, such as "Equals" or "Contains". This will open a dialog box where you can enter the value or criteria you want to filter by.
  5. Enter the value or criteria you want to filter by and click on the "OK" button. This will filter the records based on the selected column and criteria.
  6. To clear the filter, click on the "Clear Filter from" button in the "Sort & Filter" group on the "Home" tab of the ribbon. This will remove the filter and display all records.

You can also use multiple filters to further refine your results. To do this, select additional filtering options from the menu and enter the corresponding criteria. Access will apply all active filters to the records in the datasheet view.
Using the filter feature in datasheet view can help you quickly find and analyze data in your Access database. It's a powerful tool for sorting through large datasets and identifying specific records or patterns of interest.

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.

Access Filter records by selection
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.

The datasheet now only display clients
2) The datasheet now display only clients in Arizona.

Status bar of the datasheet now reads records
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.

  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.