Hello again and welcome back to our course on Access 2016.
In this section we’re going to look at sorting and filtering in Datasheet View.
And in fact both this and the next section it’s a good idea if you are working along
with me to have the Home tab selected because a lot of the things that we’re going to
do are accessible via the Home tab, although many of them can be done using just what’s
available within Datasheet View itself.
Now the first thing we’re going to look at is filtering.
At the moment we have about a dozen or so trips in our trip table.
So it’s really not difficult to see all of the trips at once.
And if I were trying to select one of these for myself I could scroll through and decide
which one looked most attractive or was within my price range or whatever.
Once you’ve got a very large number of trips to choose from, if I say wanted to find one
in my price range or going to a particular country or with a particular level of difficulty
I really wouldn’t want to have to go through hundreds, maybe thousands of these to find
the ones that match my criterion or criteria.
Filtering can help in this way because if you want to, for example, find all of the
trips that are classified as moderate you can filter to find that.
Now there is a Sort and Filter group on the Ribbon and you can do that using that group.
But you can also use the ActivityLevel column itself in Datasheet View.
If you go to the ActivityLevel column and click on the little dropdown to the right
it makes available sorts and filters.
Now I’m going to look at sorting a little bit later on in this section.
Let’s concentrate on filters.
Now at the moment the first of those filters, Select All, is selected.
If I deselect it I now have nothing selected in the ActivityLevel column.
But if what I wanted to see was the moderate trips, if I click on just Moderate and click
on OK all I can see now are the trips that are classified as Moderate.
Having applied a filter you get that little Filter icon here, like the little filter you
used to get in chemistry at school, indicating that a filter has been applied.
And if I wanted to clear the filter I click on that button again.
There’s an option there, Clear Filter from ActivityLevel, and that effectively puts it
back to no filter, which in this case is the same as all activity levels being shown.
Now filters can get to become a lot more complex than that.
So for instance if I wanted to filter by country, again click on the little icon there, the
little dropdown icon.
I’m going to deselect all here.
Well I want somewhere in the Americas so that’s Costa Rica or the U.S.A., click on OK, and
now I can see the trips that are to anywhere in the Americas.
And again I can clear that in the same way.
There are then some specialized types of filter.
Let’s go to the Description column now.
Now clearly I’m not going to match those long, long descriptions.
But what I get here with a long text field is text filters.
And text filters are much more flexible.
So for instance I could say I want to go on a trip where the description includes the
word Trek.
So the description contains Trek.
So it will look at all those descriptions and find the one with the word Trek in it.
Now notice what those text filters are.
It equals, it does not equal, it begins with something, it doesn’t begin with something,
contains, doesn’t contain, ends with, doesn’t end with.
So quite a reasonable selection of text filters.
Now you also can have numeric types of filter.
So if I wanted to filter on the number of days I could.
But let’s look at maximum price.
So let’s suppose that for me the maximum price can’t be more than $3,000.
So on PriceMaximum, Filters, I can go for Number Filters.
I’m going to say less than $3,000.
It’s actually less than or equal to $3,000.
And that limits my price range.
But I might also say well I don’t really want to go on a very cheap trip.
I’ve got a budget.
I can pay up to $3,000 and I’m prepared to pay say over $2,000.
Let’s go back in.
I don’t have to clear the existing filter.
I can just go in and set a different one.
So I could say between say $2,000 and $3,000.
And there are the trips whose maximum price is in that range.
Now that should be enough information to get you started on filters but let me just point
out a couple of the more advanced features which you may want to turn your attention
to later on.
When you have a filter applied such as this one you can toggle the filter on and off.
And there’s a Toggle Filter button in the Sort and Filter group on the Home tab on the
Ribbon.
If I click Toggle Filter now I’m back to seeing all of the records.
Click it again and the latest filter is applied.
And also Advanced.
There’s an option there to Clear All Filters.
But also there is a Filter by Form option.
And with this option you can set filters on more than one field at a time.
So you can go into a particular field and set a filter, set a filter on a different
field and come up with a more complex filter on the records in the table.
When you’ve got this option in force you can then close.
And there is an even more advanced option which is Advanced Filter Sort which lets you
set up really a quite complex system of filtering and sorting records.
Now that’s outside the scope of what we’re going to do here but in the future you may
want to look into those possibilities.
Let’s now take a look at sorting.
Now I mentioned just now the advanced filter sort and once you get used to some of the
facilities within Access 2016 you may indeed do some very complex filtering and sorting.
But for the moment I’m just going to point out basic sorting and first of all let me
point out that for a straightforward table like this one where you have a primary key
which is an AutoNumber ID and as you add records to the table they get successively larger
IDs.
The table is by default sorted by ID.
If I wanted to sort by, for example country, all I need to do is to say select Country,
click on the dropdown.
There is a Sort Ascending, that’s A to Z or a Sort Descending, sort Z to A. So ascending.
It’s now sorted by alphabetical sequence of country.
Click again, let’s have descending.
That’s sorted on descending alphabetical sequence of country name.
And of course you can also have sorts on numeric fields.
So if I wanted to sort the trips in this table according to ascending order of maximum price
click on the dropdown, Sort smallest to largest and there we are.
I’ve now switched into the Night Movies database just to point out one other thing
here.
I’m going to show you what is a little bit of a problem.
It does have a number of alternative solutions.
But let’s suppose you wanted to sort the movies in the movie database based on title.
Let’s select the Title column and then sort them into sequence, ascending sequence, A
to Z.
What you would find is that two of the movies, 12 Angry Men and 12 Years a Slave, appear
at the top of the list because when Access does alphabetical sorting it treats numeric
characters before alphas.
So 12 Angry Men and 12 Years a Slave appear first.
They don’t appear as though they’re titles were the words Twelve and then Angry Men and
the word Twelve and then Years a Slave.
Now one way around this and probably the most popular way around this is that when people
set up tables like this in a database they have another column, another field which is
called the SortTitle, the title to sort with.
Now usually the sort title is the same as the actual title.
So for A Beautiful Mind the sort title would be A Beautiful Mind.
But for 12 Angry Men the sort title will be 12 Angry Men but instead of 1-2 it would be
the word Twelve.
And then when somebody’s sorting the list of movies it’s sorted as though 1-2 was
the word Twelve.
The same approach is used to overcome another very common problem and that’s the ones
that all begin with The.
Now some people would want to see The Aviator after Terminator 2, some people would prefer
to see Aviator at the beginning in with the A’s.
So sorting can be a little bit of a problematic thing and you will need to adopt whatever
approach you think is most suitable in a given situation.
That’s sorting and filtering.
We’ve got one more section on Datasheet View where we’re going to look at data entry
and modification.
That’s the next section.
I’ll see you then.