Access 2007

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.