Access Basics:
Sort & Filter a Table

Step-by-Step: Sort & Filter a Table

---

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.

   

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 the Sort Ascending button on the toolbar.
   The records rearrange so that the TripName column is in alphabetical order.

   

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

3. 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 the Remove Filter button.

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

   

3. Click on 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 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.

   

2. Press the DELETE key. "none" vanishes from the form.
    
3. Click 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 the Apply Filter button. Only two records have a link entered.

   

6. Click on 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 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.

   

4. Click on the Apply Filter button. Two records meet the criteria.

   

5. Click on 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 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"

   

5. Click on 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"

   

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

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

How 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.

   

   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 . Drop it onto the grid in the first column.

   

4. In the Sort row, select Ascending under the field DateStarted.
    
5. In 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 the Apply Filter button.

   

8. From the menu select again  Records | Filter | Advanced Filter/Sort... 
    
9. Click 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. 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 the Apply Filter button.

    

---

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.