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:

Table Pane
  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 Access Records using Complex Criteria

  1. As with any filter, the first step is to display the table you want to work with. You want to find projects that are due before September 1st that are not already complete. That information is in the Projects table, which is shown here. To open the Advanced Filter/Sort window choose Records>>Filter>>Advanced Filter/Sort from the menu.
  2. You are now in the Advanced Filter/Sort window. The top pane is a box representing the Projects table – each field in the table appears in this box. You may have to scroll to see the ones you need, we have done this for you here. You need to define two criteria: Projects due before September 1st, this requires a criterion using the Due Date field; and Projects that are not complete, this requires a criterion using the Completed field. Records need to meet both criteria in order to be displayed on the filtered datasheet. In order to put them in the QBE grid all you need to do is double-click each field name in the table pane (top pane). Double-click on Due date, and then on Completed.
  3. You need to state the criteria in language Access can understand. Fortunately, Access almost speaks English, and it certainly knows math, so you shouldn not find criteria expressions too hard to understand. The first criterion is that the due date be before 9/1/99. Access understands that the value should be less than 9/1/99 – so the criteria is simply <9/1/99. And you need to put it in the row labeled Criteria in the same column as the Due Date field. Now click on the criteria row in the Completed column (to the right of your current field) to add your next criterion.
  4. Notice that Access put # around the date in the criteria you just typed. # is just an indication to Access that a value is a date. The next criterion is that the project be incomplete. You may remember that the Completed field appeared as a checkbox on the table datasheet – this is simply a yes/no field. Specify that the value of Completed is “no” by typing No into the Criteria row of the Completed column. The next step is to choose a sort order for the filtered records. To display the drop-down list arrow for sorting using the Due Date field, click the Sort row in the Due Date column.
    Note: Yes/No fields are stored by Access as zeros and ones (zero is false and one is true). So you could also specify that Completed must be equal to zero.
  5. To list projects by due date (with the project that is due first listed first) you need to specify an ascending order for the Due Date field.
    Select Ascending from the Sort row drop-down list.
  6. You have now specified criteria and sort order, and you’re ready to view the filtered datasheet. To do so, click the Apply Filter button on the toolbar (it looks like a funnel). Note: Like the Filter by Form window the Advanced Filter/Sort window allows you to specify Or criteria. Instead of defining them on a separate tab, use the Or row of the QBE grid.
  7. Here is the filtered datasheet. In fact, since there are only two projects that need to be completed before September 1st, so you didn’t really need to sort the datasheet--but now you know how to do it. As usual with filtered datasheets, to see all records, click the Remove Filter button on the toolbar.

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.

Matching the task to the appropriate filtering or sorting action.

Complex Access Criteria

The correct matching pairs:
  1. Find your one client in Casa Grande: Use the Find and Replace dialog box.
  2. Find the earliest start date in the Projects table: Sort the table in ascending order
  3. Find projects for Lizard Web, Inc.: Use Filter by Selection
  4. Find projects for Network Consultants that are not complete: Use Filter by Form
  5. Find projects due in 2000 that have a start date in 1999: Use Advanced Filter/Sort
Queries draw various data sources together and present the combined information in useful views. They enable you to synthesize the raw data in your Access tables into meaningful analysis.
Queries are an essential part of any database application and are the tools that enable you and your users to extract data from multiple tables, combine it in useful ways, and present it to the user as a datasheet, on a form, or as a printed report.
"Queries convert data to information." To a certain extent, this statement is true. The data contained within tables is not particularly useful because, for the most part, the data in tables appears in no particular order. Also, in a properly normalized database, important information is spread out among a number of different tables. Queries are what draw these various data sources together and present the combined information in such a way that users can actually work with the data.

The word query comes from the Latin word quaerere, which means "to ask or inquire." Over the years, the word query has become synonymous with quiz, challenge, inquire, or question. An Access query is a question that you ask about the information stored in Access tables. You build queries with the Access query tools. Your query can be a simple question about data in a single table, or it can be a more complex question about information stored in several tables. For example, you might ask your database to show you only trucks that were sold in the year 2013. After you submit the question in the form of a query, Access returns only the information you requested.