Access Basics:
Sort & Filter a Table

Title: Jan's Illustrated Computer Literacy 101


As you accumulate more records, you will sometimes want to sort the records into a different order or to filter the records to see only the ones that match certain rules, called criteria.

For example, you might want to sort the records by AmountPaid or ItemName or Category.

You might want to filter the records to see only records for the year 1990 or where Cost is more than $5. So many choices!

Sorting

What sorting does

Sorting puts the records in order based on the field that you select, either ascending or descending order. A text field is ordered alphabetically. A number field is ordered in numerical order. The easy way is to click a Sort button on the toolbar. Buttons: Sort Ascending, Sort Descending

  • One sort at a time
    Sorts do not build on each other. A new one replaces the old one. To sort on more than one field at once, you must use an advanced filter or a query.
  • Saved with table
    If you save your table while it is sorted, the sorting order is saved. When you open the table again, it will still be sorted.

TipNumbers in a text field will sort in alphabetical order like 1, 10, 100, 2, 20, 200... not in numerical order as 1, 2, 10, 20, 100, 200...


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access Objects Arrow: subtopic open
    Icon: StepTable: Design View
    Icon: StepTable: Datasheet View
    Icon: StepManage a Table
    Icon: StepSort & Filter a Table
    Icon: StepQuery Wizard
    Icon: StepAutoForm
    Icon: StepAutoReport
    Icon: StepData Access Page
        About Printing
    Icon: StepPrint Objects
    RelationshipsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Filtering

What a filter does
A filter hides records that do not match the criteria that you chose. A simple filter looks for records that match one value in one field. An advanced filter can have several criteria in multiple fields, combined with sorting.

  • Multiple filters form a chain
    If you apply a filter to your datasheet and then apply a new filter, you will be filtering the already-filtered records. This will likely not give you the same results as using your new filter by itself! Be sure to remove the first filter if you want to start over.
  • Saving a filter
    When you filter a table and then save the table, the last filter you used is saved with the table. When you open the table again, the filter is not automatically applied. To apply the last used filter, you must click Button: Apply Filter the Apply Filter button. You can save a filter as a query to use later.

Example: Filtering Star Wars Collectibles table in the starwars.mdb database
The example below is filtered to show only items where the Condition field has the value "Excellent". Only 3 of the 14 records in the table match this criterion.

The results are also sorted on the field EpisodeID in ascending order and the layout has been modified by changing column widths and moving some columns. The Conditionfield is now in view.

Datasheet after changing layout, filtering, sorting

After changing layout and filtering

Table Datasheet View: Original Layout

Original datasheet

How to tell when records are filtered:

  • Navigation bar says so:  (Filtered)
    Record Navigation buttons (Filtered)
  • Remove Filter button  is displayed on toolbar.
       Remove Filter button on Standard toolbar 
    The Remove Filter button's background is orange Button: Remove Filter (WinXP desktop theme) when you are use the default WinXP desktop scheme.
  • Status bar shows the mode indicator FLTR.
     Toolbar: Status Bar: FLTR on

Ways to Filter:

  • Filter By Selection
  • Filter Excluding Selection
  • Filter By Form
  • Advanced Filter grid

Filter By Selection

The easiest way to filter is by selection.

  • Select a cell with the value that you want see in the results.
  • Click on Button: Filter by selection the Filter By Selection button on the toolbar.

The records that do share the cell's value display; the rest are hidden. Simple!

Filter By Excluding Selection

  • Select a cell with the value that you do NOT want to see in the results.
  • From the menu select  Records | Filter | Filter Excluding Selection 

Your results will show all the records that do NOT match the cell that you chose.

Filter By Form

When you want to show records based on a combination of criteria, you should use Filter by Form.

  • Click the button Button: Filter by Form  Filter By Form.
    A new window appears with one row, showing each of the fields from the table.  Each field has a drop list of possible values. "Null" and "Is Not Null" are the only choices for some data types: memo, hyperlink, Yes/No, or OLE object.
  • Choose the values you want to see in the results. You can make choices for more than one field. The results must match all of the choices at once.
  • Click Button: Apply Filter - ready the Apply Filter button.

Form for Filter by Form

At the bottom of the Filter By Form window are two tabs:  Look for and Or. The choices on one tab work as AND. Any result must meet all of the criteria on that tab. But... you can make other choices on the second tab, which works as OR. The results must match what you chose on the first tab OR what you chose on the second tab.

TipIf your results do not show what you expected, check the form to see if you have choices selected on multiple tabs or for a field that is out of view. Delete any values that should not be in this filter.

As soon as you select the second tab, a third one is created. It can get complicated!

Advanced Filter Grid: Combination Sort and Filter

The Advanced Filter/Sort grid lets you sort and filter on multiple fields.

  • From the menu select  Records  |  Filter  |  Advanced Filter/Sort... 
    A new window appears with a top and bottom pane.
  • Existing filter criteria and sorting orders will show in the grid in the bottom pane.
  • Drag any other fields that you want to use for sorting or filtering from the list in the top down to the grid below.
  • Set sorting on a field in the Sort row by choosing "Ascending" or "Descending" or "(not sorted)" underneath the field name.
  • Set criteria in the Criteria rows.
    • Enter a value that you want to match in quotes, like "Excellent".
    • Enter a values that you do NOT want to see in like NOT "6.99"
    • Enter Is Null to find empty values.

Advanced Filter grid

TipAccess can usually figure out what you meant if you do not type the quotes around criteria values. But, sometimes it gets confused.

Delete a Filter

It is not obvious how to delete a filter once you've created one.

From the menu,  Records  | Filter |  Advanced Filter/Sort . From the filter's menu,  Edit | Clear Grid . All criteria and sorts are removed. Apply the filter. You are returned to the datasheet will all records showing. The Apply Filter button is no longer available, which shows you that the filter is truly gone.

When You Forget What the Filter Does...

Look at the Advanced Filter/Sort. All the sorts and filtering criteria will be there, no matter what method you used to create the filter. This can be very useful! It is very easy to forget what the last filter was. Creating a new filter may just filter the already-filtered results. That's fine if it is what you meant to do. It can be disastrous if you did not realize what had happened.


Icon Step-by-Step 

Step-by-Step: Sort & Filter a Table

 Icon Step-by-Step

What you will learn:

to sort a table datasheet on one column
to apply Filter By Selection
to apply Filter By Form
to use Filter By Form with multiple criteria
to use Advanced Filter/Sort


Start with: Access open but no database open, mytrips.mdb from previous lesson

Add More Records

It's not much fun to sort and filter with just 3 records. You need to add some more trips.

  1. Add the following trip records. (Some fields will be empty.)
    You can type the info in or copy one field at a time and paste directly into the datasheet.
     
    TripName Family Reunion - 2003
    DateStarted 5/31/2003
    DateEnded 04-Jun-03
    Description Drove to Birmingham and stayed at Grandmother's house. Lunch at the park.
    Cost $250.00
    Travel Agent none
    Diary Arrived just in time for lunch. 50 family members present plus some friends and soon-to-be family members. Played baseball, sort of, after lunch. Unusual "family" rules involving big people running bases for little people or little people running bases for big people

     

    TripName Hilton Head
    DateStarted 28-Jun-96
    DateEnded 06-Jul-96
    Description Drove to Hilton Head. Stayed at friend's time share. Beach privileges.
    Cost $1,000.00
    TravelAgent none
    Diary Were beach bunnies for 2 days. Played golf and did jet skis. Climbed the lighthouse. Saw natural history museum. Bought gorgeous shells.

     

    TripName New Orleans Convention
    DateStarted May 5, 1995
    DateEnded May 15, 1995
    Description Stayed at New Orleans Hyatt. First 3 days for High Reaches Club.
    Cost $2545
    TravelAgent Through company having the convention
    Diary Arrived late and luggage not in sight for 2 days. Did get it in time for the banquet. Took special tour of Louisiana plantation homes and the aquarium. Love the jellyfish in black light and the fancy seahorses.

    Trips table with new records

Now we can try some filtering.


Datasheet: Sort

To start with, records are all listed in the datasheet, in the order in which they were added, so the TripID field will be in numerical order. It is an AutoNumber field so it automatically numbers each new record. You can choose to sort on another field.

  1. Click in the TripName column somewhere.
     
  2. Click on Button: Sort Ascending the Sort Ascending button on the toolbar.
    The records rearrange so that the TripName column is in alphabetical order.

    Trips table, sorted ascending on TripName field

    TipSorts are saved: If you save the table at this point, it will be sorted this way when you open it later.
     

  3. Icon: Experiment Experiment - sort each column both ascending and descending.
     
  4. Sort the TripID field in ascending order. The records return to the original display order.

To sort on more than one column at a time, you must use a query or the Advanced Filter grid, which you will do shortly.


Datasheet: Filter By Selection

Another way to change the datasheet display is to apply a filter. This hides some of the records.  Filtering is more fun with more records than you have here!

When are your records filtered?
You must be alert as to whether your datasheet is filtered or not. Look at the Navigation Bar for "(Filtered)", at the Status Bar for FLTR, or at the toolbar for Button: Remove Filter the Remove Filter button.

  1. Click in the TravelAgent column in one of the cells with the value "none".
     
  2. Click on Button: Filter By Selection the Filter By Selection button. Three records match your filter.
    Notice that beside the Navigation Bar is the word "(Filtered)".

    Trips, filtered to show TravelAgent = "none"

  3. Click on Button: Remove Filter the Remove Filter button. All records reappear.

Datasheet: Filter By Form: Is Not Null

You can use a filter to check for records that have blank fields, or for records that have values in a particular field. Filter By Form makes either one easy to do.

  1. Click on the button Button: filter By Form Filter By Form.
    A one row form appears. It shows criterion for the previous filter, TravelAgent = "none.

    Notice that there are two tabs at the bottom, Look for and Or. For this section you will work on the Look for tab.

    Trips: Filter By Form: TravelAgent = "none"

  2. Press the DELETE key. "none" vanishes from the form.
     
  3. Filter by Form: Right Click Menu: Is Not NullClick on the field PhotoLink. A drop list appears. The actual values in the records do not show, only "Is Null" and "Is Not Null".  This is true for several kinds of fields: memo, hyperlink, Yes/No, or OLE object.
     
  4. Click on "Is Not Null".
     
  5. Click on Button: Apply Filter - ready the Apply Filter button. Only two records have a link entered.

    Trips: Filter By Form: PhotoLink = "Is Not Null"

  6. Click on Button: Remove Filter the Remove Filter button. All records reappear.

Datasheet: Filter By Form: Multiple Criteria (AND)

When you want the results to satisfy multiple criteria at the same time, you can use Filter By Form to choose several values on the Look For tab.

  1. Click on the button Button: filter By Form Filter By Form again.
    The previous criterion is still in place: PhotoLink = "Is Not Null"
     
  2. Change PhotoLink to "Is Null".
     
  3. In the Cost column, instead of choosing a value,  type >1000 .
    You now have set 2 criteria for the filter. You are looking for trips that cost more than $1000 and which do not have a link to photos.

    Trips: Filter By Form: PhotoLink = Is Null, Cost > 1000

  4. Click on Button: Apply Filter - ready the Apply Filter button. Two records meet the criteria.

    Trips: Filter By Form: PhotoLink = Is Null, Cost >1000

  5. Click on Button: Remove Filter the Remove Filter button. All records reappear.

Datasheet: Filter By Form: Multiple Criteria (OR)

When you want to see a combination of results that meet one set of criteria or meet a another set, you can set the different criteria on different tabs.

  1. Click on the button Button: filter By Form Filter By Form again.
     
  2. Leave the choices on the tab Look for.
     
  3. Click on the Or tab. Another Or tab appears. No choices have been made on these tabs yet.
     
  4. Select PhotoLink = Is Null and TravelAgent = "none"

    Filter By Form: PhotoLink = Is Null; TravelAgent = "none"

  5. Click on Apply Filter - ready the Apply Filter button. Four records meet the criteria set on the two tabs:
    PhotoLink = Is Null and Cost > $1000
       Or
    PhotoLink = Is Null and TravelAgent = "none"

    Filter By Form results: two sets of criteria

    The trip to Hilton Head shows up even though its Cost was $1000. Why?
    Because it has "none" under TravelAgent.

  6. Click on Button: Remove Filter the Remove Filter button. All records reappear.

TipHow to know when a filter has multiple criteria:
Look at the tabs at the bottom of the Filter By Form window. If there are only two tabs, then all of the criteria are on the Look for tab. If there are more than two tabs, then there are criteria on all but the last tab. The filtered records must meet the criteria on one of the tab.


Datasheet: Advanced Filter/Sort

To combine sorting with filtering, you can open an Advanced/Filter Sort grid. This looks a lot like a Query Design View, which you will work with in the next lesson.

  1. From the menu select  Records | Filter | Advanced Filter/Sort... 
    The grid appears with the criteria and sorting from the last filter that you applied displayed.

    Advanced Filter/Sort with criteria and sorting from the last filter used

    The grid shows only the fields that were used in the last filter and sort that you applied. The Sort row shows Ascending under the field TripName. There are two criteria rows, which correspond to the two tabs in the Filter By Form from the previous section. This view has an advantage over Filter By Form. You can easily see that there are two sets of criteria involved.
     

  2. Delete all of the fields and criteria by selecting them and pressing the DELETE key.
     
  3. Drag the field name DateStarted from the list at the top. The mouse pointer changes shape to a rectangle Pointer: Drag field shape. Drop it onto the grid in the first column.

    Dragging field from list to Advanced Fllter Design grid DateStarted field in Advanced Filter grid

  4. In the Sort row, select Ascending under the field DateStarted.
     
  5. Criteria rowIn the Criteria row in the first column, type <1/1/1999
     
  6. Press the TAB key to move out of the cell in the grid. Access automatically revises what you typed to correct format for a date in an expression: <#1/1/1999#. Cool!
     
    Your filter will look for records with a DateStarted before January 1, 1999, and will sort them in chronological order.
  7. Click on Button: Apply Filter - ready the Apply Filter button.

    Trips, filtered with Advanced Filter/Sort

  8. From the menu select again  Records | Filter | Advanced Filter/Sort... 
     
  9. Advanced Filter - drop list of fieldsClick in the second column in the Field row. A down arrow appears in the cell.
     
  10. Click on the arrow to open a list of all of the table's fields
     
  11. Click on Cost.
     
  12. Advanced Filter: DateStarted and Cost In the Criteria row under Cost, type >=1000
    Since both criteria are in the same row,  your filter will look for records that satisfy both: Cost greater than or equal to 1000 and started before Jan. 1, 1999.
  13. Click on Button: Apply Filter - ready the Apply Filter button.

    Trips - Advanced filter on DateStarted and Cost


Remove Filter

  1. Return to the Advanced Filter/Sort window.   Records  | Filter |  Advanced Filter/Sort .
  2. From the filter's menu, select  Edit | Clear Grid .
  3. Click the Apply Filter button.
    You are returned to the table, with all records showing and the Apply Filter button no longer available.
  4. Close the table. If prompted, choose to save changes.
There are more neat things to do with filters, but that's enough for now! Let's move on to another powerful part of a database - Queries.

LessonsWorking with Databases Previous Page Next Page




Teachers: Request permission to use this site with your class
 
Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~


Last updated: 30 Apr 2012