Access Filter

The Access Filter is a way of reducing the number of records in a list which do not match a given criteria. For example, you might want to view all the records for people with the surname 'Smith'. In this case Smith is the criteria to match. All other records which have records that are not Smith will be filtered out of the list.

There are a number of ways to use this feature. The way that I have found most effective is to use the Filter By Form method.

Filter applied to an Access 2007 database table

Filter By Form

If you look at the Sort and Filter group on the Home tab of the Access Ribbon, you will see a large filter icon and three smaller icons entitled Selection, Advanced, and Toggle Filter. You need to click the Advanced icon and select Filter By Form from the drop down list. Access will then open up a blank datasheet with empty cells underneath your column headings. Click on the empty cell in the Surname column, and start entering the surname Smith. As you do so, Access suggests surnames from your list based on the letters that you have typed so far. So once you get to Sm..., Access completes the rest of the name for you.

The next thing that you need to do is click the Toggle Filter icon on the Ribbon to apply the filter.

Result: a new filtered list is displayed containing all three records from your original table with the surname 'Smith'.

Filter applied to an Access 2007 database table

Next we shall take a look at Sorting Tables.