Access 2007

Introduction

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. This lesson will show you how to filter records using common filter commands. You will also learn how to use the toggle button to switch between filtered and unfiltered results, and how to perform advanced filters and save the filtered results.

Filtering records

Watch the video! (7:10min)

Download the example to work along with the video.

Filtering records

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

• Search all of the records in one table
• Find every record in that table that meets the criteria you set
• Display the results for you in the table by hiding the records that do not meet the criteria

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.

Method	Description
Filter by Field	Lists 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 Selection	Offers 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 Filter	Narrows filtered results further by performing additional filters or sorts.

Filter 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 Filter by Field

• Highlight the column for the field you want to use as a filter. In the example below, we are filtering using the State field.
• In the Sort and Filter group on the Ribbon, click on the Filter command. It looks like a funnel.
  
  Filtering by Field
• When the dialog box appears, select the value you want to use as the filter value:
  • Checking Select All will return every record in the table. However, clicking on this option when all values are checked will clear the selections.
  • Checking Blank will find only records in which the field is empty.
  • Check as many of the values as you would like to see returned in your results.
• Click OK.

Results 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:

• Use the label that appears in the navigation bar, as seen above
• Use the Toggle Filter button on the Ribbon
  
  Toggle Filter Button

Filter 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 Filter by Selection

• Place your cursor in the cell that contains the value you want to use as your filter value.
• Click on the Selection command in the Sort & Filter group on the Ribbon.
• When the list appears, choose one of the options:
  • Equals will return all records that have exactly the same value as your selected value. In the example below, this option would return records for the City of Raleigh only.
  • Does Not Equal will return every record that does not have the same value as your selected value. In the example below, this option would return all records except for the ones with Raleigh as a city.
  • Contains will return every value that has your selected value somewhere in it. In the example below, choosing Contains would return records with a City value of Raleigh, Caraleigh, or Raleighville.
  • Does Not Contain will not return records with the selected value anywhere within the search field. In the example below, clicking Does Not Contain would return all records except for records that have Raleigh in the city name—Raleigh, North Raleigh and Raleighville, for example, would not show up in the results.

Filtering by Selection of Raleigh

Sorting or filtering the filtered results

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

• In the table showing your filtered results, select the field or value you wish to use as the basis for a deeper sort or filter.
• Apply the additional filter or sort.

Another way to do the same thing is to use the Advanced Filter command.

Advanced filters

An Advanced Filter is similar to a multilevel sort in Microsoft Excel. An Advanced Filter can help you narrow down your records. This is like running a miniature query on only one table. It uses a screen that is similar to the query design screen, and can even save your results as a query so you can use them again later. Queries are covered in more detail in other lessons.

To apply an Advanced Filter

• Click on the Advanced command in the Sort & Filter group on the Ribbon.
• From the menu that opens, select the Advanced Filter/Sort option.
• The design screen will open so you can set up your filter criteria and/or sorting order for the various fields in the table.
  
  Advanced Filter Design Screen
• To sort or filter another field, drag the field name from the table at the top of the screen to the design portion of the screen in the bottom half of the window. Then:
• To sort the new field:
• Click in the Sort cell for the field.
• Choose the sort option you want to use from the drop-down list that appears.
• To filter the new field:
  • Click in the Criteria cell for the field.
  • Enter your criteria by directly typing it into the cell. Access will put quotation marks around your criteria, showing that it is searching for that exact value.

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. Like sorts, Access filters from left to right based on the order displayed on the Advanced Filter design screen.

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

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

To save the Advanced Filter

To save the Advanced Filter:

• Right click on the Advanced Filter design tab.
• Select Save.
  Saving an Advanced Filter
• If the Save as Query dialog box comes up, you must give the advanced filter a name.
  This dialog box will only appear the first time you save.

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 on your own 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.