Note: This site will be taken down July 1, 2024.
Previous
Jan's Working with Databases
Basics: Access Objects: Sort & Filter
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 2012 or where Cost is more than $500. So many choices!
Example for illustrations:
Star Wars Collectibles table in
the starwars.accdb 
database
You can download the database to follow along with the illustrations below. If you mess it up, just download again!
Original Datasheet, with no sorting or filtering
Sorting
What sorting does
Sorting puts the records in order based on the field that you select, either ascending or descending order.
Sort a text field: Alphabetical order.
Ascending: a, b, c... Descending: z, y, x...
Sort a number field: Numerical
order.
Ascending: 1, 2, 3, ..., 100, 101, 102.... Descending: 102, 101, 100, ..., 3, 2, 1
Sort numbers that are in a text field: Alphabetical order like 1, 10, 100, 2, 20, 200... instead of numerical order.
How to sort:
- Button: Click in the field you want to use to sort and
then click on a Sort button
on
the Home ribbon tab. - Context Menu: Right click in the field you want to use to sort and select a sort command.
In the context menu, for different data types the symbols are the same but the words for the commands change. This can be more helpful than the fixed text that shows on the ribbon button.
Datasheet sorted on YearMade, smallest to largest
ItemID no longer in numerical order
How to tell a datasheet is sorted:
The Sort Ascending or Sort Descending button is highlighted on the Home ribbon tab.
The color of the highlight varies with the version.- A column heading has a sort symbol with the arrow,
or 
. - The button Remove Sort is available.
When records are in the default order, the button is grayed out.
In one sense, a datasheet is ALWAYS sorted. By default the records show in the order in which they were entered. If there is an AutoNumber primary key field, then that field is in numerical order. An easy way to get back to the original order is to sort on the primary key field, if your table has an AutoNumber primary key.
How to remove a sort:
- Button: On the Home ribbon tab, click the Remove Sort button.
One sort active at a time
Sorts do not build on each other. A new one replaces the previous 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.
Filtering
What a filter does
A filter shows only records that 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, and even combine those with sorting.
The symbol for a filter is a funnel, 
.
- Multiple filters are nested
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. Also remember that the order you apply the filters can often make a big difference. - Saving a filter
When you filter a table and then save the table, the last filter you used is saved with the table. But, when you open the table again, the filter is not automatically applied. 
Apply the last used filter: Click the Toggle Filter button 
on the Home ribbon tab or the Unfiltered or No Filter button 
on the Navigation Bar for the datasheet. These buttons are not available until after a filter has been created and applied.
You can save a filter as a query to use later.
In the illustration below the datasheet 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 single criterion.
The results are also sorted on the field EpisodeID in ascending order.
After filtering. Several ways to tell that the datasheet is filtered.
How to tell when records are filtered:
Datasheet Navigation Bar says so: Filtered  'Filtered' is a toggle button which will reapply the last filter used or remove the filter. That filter is not deleted however. |
Toggle Filter button on the ribbon is highlighted.  |
Status Bar says so: Filtered   |
Column heading shows Filter icon   |
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.
-
Button: Click in a cell with the value that you want to see in the results.
Then, click on the Filter By Selection button 
on the Home ribbon tab and then on the first item in the list, Equals <what is in the cell>. Or, click the arrow on the button to open a list of options.
- Context Menu: Right click on the cell with the value you want to use to filter and choose a filter from the same list.
The datasheet refreshes and shows records that match the filter; the other records are hidden. Simple - for a simple filter on one field.
Filter By Form
When you want to filter records based on a combination of criteria on two or more fields, you must use a more advanced method. You cannot use this form to sort. The form is just a datasheet with only one row. You can enter criteria for one or many of your fields in this row.
Click the Advanced button on the Home ribbon tab in the Sort & Filter tab group.
A menu opens.-
Click on 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. You can only pick one value for the field. "Null" and "Is Not Null" are the only choices for some data types: memo, hyperlink, Yes/No, or OLE object.
Filter By Form: Filters using a single value for each of two fields
EpisodeID = A New Hope 1977 and Condition = Good - Choose the value you want to see in the results for each field that you want to use. The results must match all of the choices at once. So in the illustration, a record must have a matching EpisodeID AND must match the condition, 'Good'.
- Click the Toggle
Filter button
on the Home ribbon tab, Sort & Filter tab group, to apply the filter.
Filtered datasheet. Only the records that match all criteria on the form's 'Look for' tab.
When you want 'this' OR 'that': At the bottom of the Filter By Form window are two tabs: Look for and Or. The choices on any one tab work as AND so any result must meet all of the criteria on that tab. But... you can make other choices on the second tab, so that the filtered results must match what you chose on the first tab OR what you chose on the second tab. As soon as you select the second tab, a third blank one is created. It can get complicated!
Strange Results: If 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 the filter.
Advanced Filter Grid: Combination Sort and Filter
The Advanced Filter/Sort grid lets you combine sorts and filters and use multiple fields.
-
Click the Advanced button on the Home ribbon tab in the Sort & Filter tab group,and then Advanced Filter/Sort.
A new tab appears next to the datasheet tab. It has a top and bottom pane. In fact, this is like a query's design view.
Current filter criteria and sorting orders will show in the grid in
the bottom pane.- You can drag any other fields that you want to use for sorting or filtering from the list in the top down to the grid below.
- Sort row: Set sorting on a field by choosing "Ascending" or "Descending" or "(not sorted)" underneath the field name.
- 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.
- Values on the same Criteria row work together as AND.
- Values on separate rows work as OR.
The illustration shows a filter that will show any item that is from episode "A New Hope 1997" and is in 'Good' condition OR is from "The Phantom Menace 1999" and was a gift.
Using quotes around values: Access can
usually figure out what you meant if you do not type the quotes around criteria
values and will add them for you. But, sometimes it gets confused. It is a good idea to get into the habit of typing those quotes yourself.
Delete a Filter
It is not obvious how to completely delete a filter instead of just toggle it off.
- In the Advanced view, from the ribbon, Advanced button > Clear Grid.
All criteria and sorts are removed but the filter window is still open. - Click the Toggle Filter button on the ribbon.
You are returned to the datasheet will all records showing.
The Toggle Filter button is no longer available, which shows you that the filter is truly gone. The filter tab is still open so you can create a new filter in it.
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.
 |
Step-by-Step: Sort & Filter a Table |
|
| What you will learn: | to add records to an existing table datasheet 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: AND, OR to use Advanced Filter/Sort to remove a filter |
Start with: 
,
, Access open but no database open,mytrips-Lastname-Firstname.accdb 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.
-
Back up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.
Access 2007: 
> Manage > Back Up Database
Access 2010: File > Save and Publish > Back Up Database > Save As button
Access 2013: File > Save As > Back Up Database > Save As button 
Open the Trips table and add the following trip records.
The PhotoLink field is empty for these records.
You can type the info in or copy one value at a time and paste directly into the datasheet. Notice how Access reformats the dates automatically to the format in the field's properties.
Field Value TripName Family Reunion - 2003 PhotoLink 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. Field Value TripName Hilton Head PhotoLink 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 Travel Agent none Diary Were beach bunnies for 2 days. Played golf and did jet skis. Climbed the lighthouse. Saw natural history museum. Bought gorgeous shells. Field Value TripName New Orleans Convention PhotoLink DateStarted May 5, 1995 DateEnded May 15, 1995 Description Stayed at New Orleans Hyatt. First 3 days for High Reaches Club. Cost $2545 Travel Agent 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. 
Datasheet after entering 3 new records.
Now we can try some sorting and 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.
- Click in the TripName column somewhere.
- Click on
the Sort Ascending button on the ribbon.
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.
-
Experiment: Sorting- Sort each column, both ascending and descending.
- Which columns cannot be sorted? (The sort buttons are grayed out.)
In Datasheet View, each new sort replaces the old one. You cannot use a multiple sort directly in the Datasheet view, like LastName, then FirstName, then MiddleName.
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.
-
Click the button on the Home ribbon tab:
Access 2007: Clear All Sorts 
Access 2010: Remove Sort
Access 2013, 2016: Remove Sort 
The records return to the original display order with TripID in numerical order.
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:
Navigation Bar
Status Bar
Ribbon 
Column Heading
- Click in the TravelAgent column in one of the cells with the value
"none".
- Click on the
Selection button .
A list opens. 
Click on Contains 'none'.
Three records match your filter.
Notice that beside the Navigation Bar is the word "(Filtered)".
Four ways to know the datasheet is filtered: TravelAgent=none
- Inspect the window for clues that this datasheet is filtered.
Can you find all four? Toggle button, Navigation Bar, Status Bar, Column Heading.
- Click on the
Toggle Filter button on the Home ribbon tab, .
All records reappear. - Inspect the window for clues about whether filter is available or not.
There are two, which tell us that a filter is available but is not currently active:- Toggle Filter button is available but is not colored.
- Navigation Bar shows 'Unfiltered'
.
- Toggle Filter button is available but is not colored.
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.
- Click on the button Advanced to open its list and then on Filter By Form.
A one row form appears. It shows the single 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.
- Press the DELETE key.
Like "none" vanishes from the form.
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, OLE
object, and Attachment.
- Click on "Is Not Null".
- Click on the
Toggle Filter button.
Only two records have a link entered.
- Click on the highlighted Toggle Filter button.
All six records reappear and the Toggle Filter button loses its color.
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.
- Open the Filter By Form again.
The previous criterion is still in place: PhotoLink = "Is Not Null"
- Change PhotoLink to "Is Null".
- 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.
- Click on the
Toggle Filter button.
Two records meet the criteria - Alaska and New Orleans.
- Click on the Toggle Filter button.
All six 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.
- Open Filter By Form again.
- Leave the choices on the tab Look for.
- Click on the Or tab at the bottom of the window.
Another Or tab appears. No choices have been made on these tabs yet.
- Select PhotoLink = Is Null and TravelAgent = "none"
- Click on the
Toggle 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 results are shown in the original order, not by which set of criteria they met.
The Alaska and New Orleans trips meet the criteria on the Look for tab.
The Family Reunion and Hilton Head trips meet the criteria on the Or tab. - Click on the Toggle Filter button.
All six 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 tabs.
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.
- On the ribbon, click on the button Advanced and then on Advanced Filter/Sort...
The grid appears with the criteria and sorting from the last filter that you applied displayed. Notice that the filter has its own tab in the Object window. Each time you click Advance Filter/Sort, a new tab will appear.
The grid includes only the fields that were used in the last filter. 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.
If you had applied a sort, it would show on the Sort line of the grid.
Hover over the column PhotoLink until the mouse pointer changes to the Select Column shape, 
. - Drag to the right to select all three columns with field names and criteria.
- Press the DELETE key.
The grid is now blank.
- Drag the field name DateStarted from the
list at the top.
The mouse pointer changes shape to the dragging shape
. - Drop it onto the grid in the first column.
Click in the Sort row under the field DateStarted.
An arrow appears at the right side of the cell.- Click
on the arrow.
A list of choices appears. - Click on Ascending.
In
the Criteria row in the first column, type <1/1/1999 .
-
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# in Access 2007 and 2010 and <#01/01/99# in Access 2013 and 2016. Very helpful!Your filter will now look for records with a DateStarted before January 1, 1999, and it will sort them in chronological order.
- Click on the
Toggle Filter button.
The datasheet is now filtered to show the three records that have a DateStarted before 1/1/1999. The records are sorted on the DateStarted field.
- Open the Advanced Filter/Sort again.
Click in the second column in the Field row.
A down arrow appears at the right edge of the cell.- Click on the arrow to open a list of all of the table's
fields
- Click on 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. - Click on the
Toggle Filter button.
Only two records meet both criteria.
Remove Filter
It is easy to toggle a filter off but that does not get rid of the filter entirely. It's still in the background, ready to be used again.
Click the Advanced Filter button again.- In the menu, click on Clear All Filters.
- Inspect the datasheet.
All of those clues that the datasheet is filtered are gone.
But the records are still sorted. Unexpected! - Click on the button Remove Sort.
Now the datasheet is back to the original order and the Toggle Filter and Remove Sort buttons are grayed out. That means no sorting and no filters are available. Success! - Close the table datasheet.
If prompted, choose to save changes.
Alternate method: Click on the title of the table in filter, TripsFilter1. The grid shows in the window. Click on Advance Filter/Sort... and in the menu click on Clear Grid. (You must be viewing the filter grid for this command to be available). No more filter criteria are left, but the Sort choices are still there. Change any sorts to 'not sorted'.
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.
| © 1997-2018 Jan Smith All Rights Reserved |
Site Map What's New |
Privacy Policy Terms of Service Copyright Acknowledgements |
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~
Last updated: September 23, 2018