Access 2007[c] The Missing Manual

2017-07-07 02:10:07

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 1.2.4.2 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).

Figure 3-7. You could easily overlook the navigation buttons at the bottom of the datasheet. These buttons let you jump to the beginning and end of the table, or, more interestingly, head straight to a record at a specific position. To do this, type the record number (like "4") into the box (where it says "3 of 6" in this example), and then hit Enter. Of course, this trick works only if you have an approximate idea of where in the list your record's positioned.

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.

3.2.1. Sorting

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

Figure 3-8. This text field gives you the choice of sorting alphabetically from the beginning of the alphabet (A to Z) or backward from the end (Z to A). The menu also provides filtering options, which are described in Section 3.2.2.

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

Data Type	Sort Options	Description
Text, Memo, and Hyperlink	Sort A to Z Sort Z to A	Performs an alphabetic sort (like the dictionary), ordering letter by letter. The sort isn't case-sensitive, so it treats "baloney" and "Baloney" the same.
Number, Currency, and AutoNumber	Sort Smallest to Largest Sort Largest to Smallest	Performs a numeric sort, putting smaller numbers at the top or bottom.
Date/Time	Sort Oldest to Newest Sort Newest to Oldest	Performs a date sort, distinguishing between older dates (those that occur first) and more recent dates.
Yes/No	Sort Selected to Cleared Sort Cleared to Selected	Separates the selected from the unselected values.

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.

UP TO SPEED

Numbers and Special Characters in Text Fields

Text sorts can be a little counterintuitive, especially if you have a text field that includes numeric content.

Ordinarily, when you sort two numbers (like 153 and 49), the numbers are arranged from smallest to largest (49, 153). However, a text sort doesn't work this way. When Access performs a text sort, it examines the text character by character, which means it sorts numbers based on the first digit . If the first digit's the same, then it checks the second digit, and so on. As a result, if you sort 49 and 153 alphabetically, you get 153, 49, because 4 (the first digit in 49) is larger than 1 (the first digit in 153).

Life gets even more interesting if you throw punctuation and other special characters into the mix. Here's the order in which Access sorts everything (in a standard A-to-Z sort):

Blank (empty) values

Space

Special characters (like punctuation)

Letters

Numbers

3.2.1.1. 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:

Choose Home Sort & Filter Clear All Sorts .

Access reverts your table to its original, unsorted order.

Use the drop-down column menu to apply the sub-sort that you want for duplicates .

If you want to perform the phone book sort ( names are organized by last name, then first name), you need to turn on sorting for the FirstName field. Section 3.2 explains the sorting options you'll see, depending on the data type.

Use the drop-down column menu to apply the first level sort .

In the phone book sort, this is the LastName field.

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

3.2.2. Filtering

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.

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

Figure 3-9. This list of eligible bachelors is sorted first by height (in descending largest-to-smallest order), and then filtered to include only those hopefuls who live in the state of New York. A checkmark indicates that records that have this value are included in the datasheet. Others are hidden from view.

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

3.2.2.2. 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).

Figure 3-10. Depending on the data type, you see slightly different filtering options. For a text field (like the City field shown here), you have the option to include only the records that match the current value (Equals "Chicago"), or those that don't (Does Not Equal "Chicago"). You also have some extra filtering options that go beyond what a quick filter can donamely, you can include or exclude fields that simply contain the text "Chicago." That filter condition applies to values like "Chicagoland" and "Little Chicago."

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:

Text-based data types . You can filter values that match exactly, or values that contain a piece of text.

Numeric data types . You can filter values that match exactly, or numbers that are smaller or larger than the current number.

Date data types . You can filter values that match exactly, or dates that are older or newer than the current date.

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.

Figure 3-11. Right next to the navigation controls at the bottom of your datasheet is a Filtered/Unfiltered indicator that tells you when filtering's applied. You can also use this box to quickly switch your filter on and offclicking it once removes all filters, and clicking it again reapplies the most recent set of filters.

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

Figure 3-12. Top: With a numeric field like this PurchasePrice field, filtering by condition lets you look at values that fall above a certain minimum.

Bottom: Once you've chosen the type of filter you want, you need to supply the information for that filter. If you choose Greater Than, then you need to supply the minimum number. Records that are equal to or larger than this value are shown in the datasheet.

Here's a quick overview that describes the extra options you get using filter by condition, depending on your data type:

Text-based data types . All the same options as filter by selection, plus you can find values that start with specific text, or values that end with certain text.

Numeric data types . All the same options as filter by selection, plus you can find values that are in a range, meaning they're greater than a set minimum but smaller than a set maximum.

Date data types . All the same options as filter by selection, plus you can find dates that fall in a range, and you can chose from a huge list of built-in options, like Yesterday, Last Week, Next Month, Year to Date, First Quarter, and so on.

3.2.3. Searching

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.

POWER USERS' CLINIC

Filters vs. Queries

If you use filters frequently, you're sure to run into a problem. Access stores only one set of filtersthe filters you're currently using. In other words, once you apply a different filter, your original filter's gone and you need to reapply from scratch the next time you need it. In most cases, reapplying a filter isn't difficult. But if you've spent a considerable amount of effort crafting the perfect set of filter conditions, and you know you want to use them later, it's frustrating.

If you find yourself in this situation, you're overusing filters. Instead of relying on filters to show the information you're interested in, you'd be better off creating a separate, reusable query . Like filters, queries let you see a subset of your data based on certain conditions. Unlike filters, queries can contain much more sophisticated logic, they can leave out columns you're not interested in, and Access saves them as separate database objects so you can always reuse them later. You'll start using queries in Chapter 6.

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.

Figure 3-13. Here, a search is being performed for the word "bobblehead." If you find a match, you can keep searchingjust press Enter again to jump to the next match. In this example, pressing Enter sends Access to the next record's Description field.

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:

Choose Home Sort & Filter Find. (Or, just use the shortcut Ctrl+F.)

The Find and Replace dialog box appears (Figure 3-14).

Figure 3-14. The Find and Replace dialog box is the perfect tool for hunting for lost information.

Specify the text you're searching for in the Find What box, and then set any other search options you want to use :
- Find What . The text you're looking for.
- Look In . Allows you to choose between searching the entire table or just a single field.
- Match . lets you specify whether values need to match exactly. Use Whole Field to require exact matches. Use Start of Field if you want to match beginnings (so "bowl" matches "bowling"), or Any Part of Field if you want to match text anywhere in a field (so "bowl" matches "League of extraordinary bowlers").
- Search . Sets the direction Access looks: Up, Down, or All (which loops from the end of the table to beginning, and keeps going until it has traversed the entire table).
- Match Case . If selected, finds only matches that have identical capitalization. So "banana" doesn't match " BANANA ."
- Search Fields as Formatted . If selected, means Access searches the value as it appears on the datasheet. For example, the number 44 may appear in a Currency field as $44.00. If you search for 44, you always find what you're looking for. But if you search for the formatted representation $44.00, you get a match only if you have Search Fields as Formatted switched on. In extremely large tables (with thousands of records), searches may be faster if you switch off Search Fields as Formatted.
Note: In order to turn off Search Fields as Formatted, you must choose to search a single field in the Look In box. If you are searching the entire table, then you must search the formatted values.

Click Find Next .

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.

GEM IN THE ROUGH

Find and Replace

The search feature doubles as a powerful (but somewhat dangerous) way to modify records.

Initially, when the Find and Replace dialog box appears, it shows the Find tab. However, you can click the Replace tab to be able to find specific values and replace them with different text. All the settings for a replace operation are the same as for a find operation, except you have an additional text box, called Replace With, to supply the replacement text.

The safest way to perform a replace operation is to click the Find Next button to jump to the next match. At this point, you can look at the match, check that you really do want to modify it, and then click Replace to change the value and jump to the next match. Repeat this procedure to move cautiously through the entire table.

If you're a wild and crazy skydiving sort who prefers to live life on the edge, you can use the Replace All button to change every matching value in the entire table in a single step. Although this procedure's ridiculously fast, it's also a little risky. Replace operations can't be reversed (the Undo feature's no help here because it can reverse only a single record change), so if you end up changing more than you intend, there's no easy way back. If you're still seduced by the ease of a Replace All, consider creating a backup of your database file (Section 1.3.1) before going any further.