3.2. Datasheet Navigation
In Chapter 1, you learned the basics of moving around the datasheet. Using your mouse and a few select keystrokes, you can cover a lot of ground. (Refer back to Section 18.104.22.168 for a review of the different keys you can use to jump from place to place and perform edits.)
However, you haven't seen a few tricks yet. One's the timesaving record navigation buttons at the bottom of the datasheet (Figure 3-7).
Several more datasheet features help you orient yourself when dealing with large amounts of data, including sorting (which orders the records so you can see what you want), filtering (which cuts down the data display to include only the records you're interested in), and searching (which digs specific records out of an avalanche of data). You'll try all these features out in the following sections.
In some cases, you can most easily make sense of a lot of data by putting it in order. You can organize a customer list by last name , a product catalog by price, a list of wedding guests by age, and so on.
To sort your records, pick a column you want to use to order the records. Click the drop-down arrow at the right edge of the column header, and then choose one of the sort options at the top of the menu (see Figure 3-8).
Depending on the data type of field, you'll see different sorting options, as explained in Table 3-1. (You can also apply the same types of sort using the commands in the ribbon's Home Sort & Filter section.)
Table 3-1. Sorting Options for Different Data Types
In an unsorted table, records are ordered according to when they were created, so that the oldest records are at the top of the datasheet, and the newest at the bottom. Sorting doesn't change how Access stores records, but it does change the way they're displayed.
Tip: Use the Home Sort & Filter Clear All Sorts command to return your table to its original, unsorted order.
doesn't relocate the row to the C section. Instead, the changed row remains in its original place until you resort the table. Similarly, any new records you add stay at the end of the table until the next sort (or the next time the table is opened). This behavior makes sense. If Access relocated rows whenever you made a change, you'd quickly become disoriented.
Note: The sorting order's one of the details that Access stores in the database file. The next time you open the table in Datasheet view, Access automatically applies your sort settings.
22.214.171.124. Sorting on multiple fields
If a sort finds two duplicate values, there's no way to know what order they'll have (relative to one another). If you sort a customer list with two "Van Hauser" entries in it, then you can guarantee that sorting by last name will bring them together, but you don't know who'll be on top.
If you want more say in how Access treats duplicates, then you can choose to sort based on more than one column. The traditional phone book, which sorts people by last name and then by first name, is a perfect example of this. People who share the same last name are thus grouped together and ordered according to their first name, like this:
Smith, Star Smith, Susan Smith, Sy Smith, Tanis
In the datasheet, sorts are cumulative , which means you can sort based on several columns at the same time. The only trick's getting the order right. The following steps take you through the process:
You can extend these steps to create sorts on more fields. Imagine you have a ridiculously large compendium of names that includes some people with the same last and first name. In this case, you could add a third sortby middle initial. To apply this sort, you'd switch sorting on in this order: MiddleInitial, FirstName, Last-Name. You'll get this result:
Smith, Star Smith, Susan K Smith, Susan P Smith, Sy
In a table with hundreds or thousands of records, scrolling back and forth in the datasheet is about as relaxing as a pneumatic drill at 3:00 a.m. Sometimes, you don't even need to see all the records at oncethey're just a finger-tiring distraction from the data you're really interested in. In this case, you should cut the datasheet down to just the records that interest you, with filtering .
In order to filter records, you specify a condition that record must meet in order to be included in the datasheet. For example, an online store might pick out food items from a full product catalog, a shipping company might look for orders made last week, and a dating service might hunt down bachelors who don't live with their parents. When you apply a filter condition, you end up hiding all the records that don't match your requirements. They're still in the tablethey're just tucked neatly out of sight.
Access has several different ways to apply filters. In the following sections, you'll start with the simplest, and then move on to the more advanced options.
126.96.36.199. Quick filters
A quick filter lets you choose what values you want to include and which ones you want to hide, based on the current contents of your table. To apply a quick filter, choose the column you want to use, and then click the drop-down arrow at the column header's right edge. You'll see a list of all the distinct values in that column. Initially, each value has a checkmark next to it. Clear the checkmark to hide records with that value. Figure 3-9 shows an example where a sort and filter are being used at the same time.
Note: To remove all the filters on a column (and show every record in the datasheet), click the drop-down button at the right edge of the column header, and then choose "Clear filter."
Not all data types support filtering. Data types that do include Number, Currency, AutoNumber, Text, Hyperlink, Date/Time, and Yes/No. Memo fields don't support quick filters (because their values are typically too large to fit in the drop-down list), but they do support other types of filters.
You can apply quick filters to more than one column. The order in which you apply the filters doesn't matter, as all filters are cumulative , which means you see only records that match all the filters you've set. You can even use quick filters in combination with the other filtering techniques described in the following sections. To remove your filters, choose Home Sort & Filter Remove Filter.
Tip: Quick filters work best if you have a relatively small number of distinct values. Limiting people based on the state they live in is a great choice, as is the political party they support or their favorite color . It wouldn't work as well if you wanted to cut down the list based on birth date, height, or weight, because there's a huge range of different possible values. (You don't need to give up on filtering altogetherrather, you just need to use a different type of filter.)
188.8.131.52. Filter by selection
Filter by selection lets you apply a filter based on any value in your table. This choice is handy if you've found exactly the type of record you want to include or exclude. Using filter by selection, you can turn the current value into a filter without hunting through the filter list.
Here's how it works. First, find the value you want to use for filtering in the datasheet. Right-click the value, and then choose one of the filter options at the end of the menu (see Figure 3-10).
All data types that support filtering allow you to filter out exact matches. But many also give you some additional filtering options in the right-click menu. Here's what you'll see for different data types:
Finally, to get even fancier, you can create a filter condition using only part of a value. If you have the value "Great at darts" in the Description field in your table of hopeful bachelors, you can select the text "darts," and then right-click just that text. Now you can find other fields that contain the word "darts." This ability is what gives the filter "by selection" feature its name.
Access makes it easy to switch filtering on and off at a moment's notice. Figure 3-11 shows how.
184.108.40.206. Filter by condition
So far, the filters you use have taken the current values in your table as a starting point. But if you're feeling confident with filters, you may be ready to try a more advanced approach: filtering by condition . When you use a filter by condition, you can define exactly the filter you want.
Imagine you want to find all the rare wine vintages in your cellar with a value of more than $85. Using the filter-by-selection approach, you need to start by finding a wine with a value of $85, which you can use to build your condition. But what if there isn't any wine in your list that has a price of exactly $85, or what if you just can't seem to find it? A quicker approach is defining the filter condition by hand.
Here's how it works. First, click the drop-down arrow at the right edge of the column header. But instead of choosing one of the quick filter options, look for a submenu with filtering options. This menu's named according to the data, so text fields include a Text Filters option, number fields have a Number Filters option, and so on. Figure 3-12 shows an example.
Here's a quick overview that describes the extra options you get using filter by condition, depending on your data type:
Access also provides a quick search feature that lets you scan your datasheet for specific information. Whereas filtering helps you pull out a batch of important records, searching's better if you need to find a single detail that's lost in the mountains of data. And while filtering changes the datasheet view by hiding some records, searching leaves everything as is. It just takes you to the data you want to see.
The quickest way to search is through the search box next to the record navigation controls (see Figure 3-13). Just type in the text you want to find. As you type, the first match in the table is highlighted automatically. You can press Enter to search for subsequent matches.
When performing a search, Access scans the table starting from the first field in the first record. It then goes left to right, examining every field in the current record. If it reaches the end without a match, then it continues to the next record and checks all of its values, and so on. When it reaches the end of the table, it stops.
If you want to change the way Access performs a search, you'll need to use the Find feature instead:
Access starts searching from the current position. If you're using the standard search direction (Down), Access moves from left to right in the current record, and then down from record to record until it finds a match.
When Access finds a match, it highlights the value. You can then click Find Next to look for the next match, or Cancel to stop searching.