Finding, Filtering, Sorting  «Prev  Next»

Lesson 6Filter records using complex criteria
ObjectiveUse the Advanced Filter/Sort feature.

Filter Records using Complex Criteria

Advanced Filter Uses
So far all you have done is find records that are like some other record in the table. But often criteria are more complex than that. For instance, you might want to find invoices for more than $1000 that are overdue, or projects that you need to finish in the next month. The Advanced Filter/Sort feature is a good introduction to queries, where you can ask complex questions of your database. Advanced Filter/Sort enables you to ask those complex questions: that is, create complex criteria, but the data that you are querying must be in one table. If it's not, you need to use the regular query feature.
Whether you are using a query or an Advanced Filter/Sort, you need to define a new datasheet based on the table, choose which fields you want to display, and specify criteria that must be passed in order for a record to be displayed on the datasheet.
Here is the Advanced Filter/Sort window and it looks a lot like the Query window, so if you learn about its features you will be ahead of the game when we get to queries. The following diagram contains information with respect to table name and fields:

  1. Table pane: In a query multiple tables may appear here. Advanced Filter/Sort allows you to work with only one table.
  2. Query by Example (QBE) pane: Use this pane to define your filtered datasheet.
  3. Table name and fields: The table that you are filtering appears here with all fields listed. (You may have to use the scrollbar to see them all.

Table Pane Query by Example
Using the Query by Example (QBE) grid you can create more specific criteria than you were able to when you used the Filter by Form window. This Simulation shows you how to go about creating an Advanced Filter to find the records you need.

Filter Records via Complex Criteria
If you create a useful filter that you might want to use again you should save it as a query by clicking the Save As Query button on the Advanced Filter/Sort window. As long as you have not defined a new filter (using any of the three methods you learned in this module), your filter will still be defined when you select Records>>Filter>>Advanced Filter/Sort. Review key glossary terms from this module, and try your hand at filtering and sorting the data in the Consulting database.

Complex Access Criteria

Try your hand at matching the task to the appropriate filtering or sorting action.
Complex Access Criteria