Access 2010

Filtering records

Filters allow you to view only the data you want to see. When you create a filter, you set criteria for the data you want to display. The filter then searches all of the records in the table, finds the ones that meet your search criteria, and temporarily hides the ones that don't.

Filters are useful because they allow you to focus in on specific records without being distracted by the data you're uninterested in. For instance, if you had a database that included customer and order information, you could create a filter to display only customers living within a certain city, or only orders that contain a certain product. Viewing this data with a filter would be far more convenient than searching for it in a large table.  

In our examples and explanations, we will be applying filters to tables. However, you can apply filters to any Access object. The procedure is largely the same.

To create a simple filter:

1. Click the drop-down arrow next to the field you would like to filter by. We will filter by city, as we want to see a list of customers who live in a certain city.
   Selecting a field to sort by
2. A drop-down menu with a checklist will appear. Only checked items will be included in the filtered results. Use the following options to determine which items will be included in your filter:
   
   • Select and deselect items one at a time by clicking their check boxes. Here, we will deselect all of the options except for Cary.
   • Click Select All to include every item in the filter. Clicking Select All a second time will deselect all items.
   • Click Blank to set the filter to find only the records with no data in the selected field.
   Setting the filter to only show records with "Cary" in the city field
3. Click OK. The filter will be applied. Our customers table now displays only customers who live in Cary.
   The filtered table, now showing only the records for customers who live in Cary

Toggling your filter allows you to turn it on and off. To view records without the filter, simply click the Toggle Filter command. To restore the filter, simply click it again.

Removing the current filter with the Toggle Filter command

Creating a filter from a selection

Filtering by selection allows you to select specific data from your table and find data that is similar or dissimilar to it. For instance, if you were working with a bakery's database and wanted to search for all products whose names contained the word "chocolate", you could select that word in one product name and create a filter with that selection. Creating a filter with a selection can be more convenient than setting up a simple filter if the field you're working with contains many items.

To create a filter from a selection:

1. Select the cell or data you would like to create a filter with. We want to see a list of all of our products that contain the word "chocolate" in their names, so we'll select the word "chocolate" in the Product Name field.
   Selecting text to filter for
2. Select the Home tab on the Ribbon, and locate the Sort & Filter group.
3. Click the Selection drop-down arrow.
   The  Filter by Selection command
4. Select the type of filter you would like to set up:
   • Contains includes only records with cells that contain the selected data. We'll select this, since we want to see records that contain the word "chocolate" anywhere in the title.
   • Does Not Contain includes all records except those with cells that contain the selected data.
   • Ends With includes only records whose data for the selected field ends with the search term.
   • Does Not End With includes all records except those whose data for the selected field ends with the search term.
   Setting the filter to show only records that contain the selected word
   
5. The filter will be applied. Our table now displays only products with the word "chocolate" in their names.
   The filtered table, now showing only records containing "chocolate" in the Product Name field