Chapter 11

Finding, Filtering, and Sorting Your Data — Fast

In This Chapter

Locating data with the Find command

Sorting your database

Filtering by selection

Filtering by form

You probably already know what databases do. They help you store, organize, view, and document the information that’s important to you — your personal information, your business-related information, any kind of information you need to keep track of.

Of course, this is not a new concept. People have been storing information for as long as there’ve been people. From making scratches in the dirt to keep count of the number of sheep in the flock to handwritten census information kept in huge ledgers to metal filing cabinets filled with typed lists and reports, man has been using some form of database for a long, long time.

Of course, things are a lot easier now. You can store millions of records in a single computer, and people all over the world can access them, assuming they have permission. Farmers can keep track of their sheep, countries can keep track of their populations, and you can keep track of your friends, family, employees, products, and holiday card lists — anything your little data-driven heart desires.

But what is all this “keeping track” of which I speak? It’s not just storing the data; it’s getting at it when you need it. Thanks to the magic of the Find, Sort, and Filter commands, Access tracks and reorganizes the stuff in your tables faster than ever, putting it literally at your fingertips whenever you need to locate one or more of the pieces of information you’re storing. When you need a quick answer to a simple question, these three commands are ready to help. This chapter covers the commands in order, starting with the speedy Find, moving along to the organizational Sort, and ending with the flexible Filter.

Find, Sort, and Filter do a great job with small questions (such as “How many customers from France didn’t give us a phone number?”). Answering big, complex questions (such as “How many people attended Major League Baseball games in July and also bought a team hat?”) still takes a full-fledged Access query (in this case, querying a database of baseball-game attendees and the database of items sold in the team store). Don’t let that complexity worry you, though, because you can flip back to Chapter 10, which explains queries in exciting detail.

Using the Find Command

When you want to track down a particular record right now, creating a query for the job is overkill. Fortunately, Access has a very simple way to find one specific piece of data in your project’s tables and forms: the Find command.

Find is found — big surprise here — in the Find section of the Home tab, accompanied by a binoculars icon. You can also get to Find by pressing Ctrl+F to open the Find dialog box.

Although the Find command is pretty easy to use, knowing a few tricks makes it even more powerful, and if you’re a Word or Excel user, you’ll find the tricks helpful in those applications, too — as the Find command is an Office-wide feature. After you get through the Find basics (covered in the next section), check the tips for fine-tuning the Find command in the Shifting Find into high gear section, later in this chapter.

Finding anything fast

Using the Find command is a very straightforward task. Here’s how it works:

1. Open the table or form you want to search.

Note that the Find command works in Datasheet view and with Access forms and becomes available as soon as a table or form is opened.

If you want to dive into forms right now, flip back to Chapter 7.

2. Click in the field that you want to search.

The Find command searches the current field in all the records of the table, so be sure to click the right field before starting the Find process. Access doesn’t care which record you click; as long as you’re on a record in the right field, Access knows exactly which field you want to Find in.

3. Start the Find command.

You can either click the Find button in the Find section of the Home tab or press Ctrl+F.

The Find and Replace dialog box opens, ready to serve you.

4. Type the text you’re looking for in the Find What box, as shown in Figure 11-1.

Take a moment to check your spelling before starting the search. Access is pretty smart, but it isn’t bright enough to figure out that you actually meant plumber when you typed plumer.

Figure 11-1: The Find and Replace dialog box.

5. Click Find Next to run your search.

• If the data you seek is in the active field, the Find command immediately tracks down the record you want.

The cell containing the data you seek is highlighted.

What if the first record that Access finds isn’t the one you’re looking for? Suppose you want the second, third, or the fourteenth John Smith in the table? No problem; that’s why the Find and Replace dialog box has a Find Next button. Keep clicking Find Next until Access either works its way down to the record you want or tells you that it’s giving up the search.

• If Find doesn’t locate anything, it laments its failure in a small dialog box, accompanied by the sad statement

Microsoft Access finished searching the records. The search item was not found.

If Find didn’t find what you were looking for, you have a couple of options:

You can give up by clicking OK in the small dialog box to make it go away.

You can check the search and try again (you’ll still have to click OK to get rid of the prompt dialog box). Here are things to check for once you’re back in the Find and Replace dialog box:

• Make sure that you clicked in the correct field and spelled everything correctly in the Find What box.

You can also check the special Find options covered in the following section to see whether one of them is messing up your search.

• If you ended up changing the spelling or options, click Find Next again.

Shifting Find into high gear

Sometimes just typing the data you need in the Find What box doesn’t produce the results you need:

You find too many records (and end up clicking the Find Next button endlessly to get to the one record you want).

The records that match aren’t the ones you want.

The best way to reduce the number of wrong matches is to add more details to your search, which will reduce the number of matches and maybe give you just that one record you need to find.

Access offers several tools for fine-tuning a Find. To use them, open the Find and Replace dialog box by either

Clicking the Find button on the Home tab

or

Pressing Ctrl+F

The following sections describe how to use the options in the Find and Replace dialog box.

If your Find command isn’t working the way you think it should, check the following options. Odds are that at least one of these options is set to exclude what you’re looking for.

Look In

By default, Access looks for matches only in the current field — whichever field you clicked in before starting the Find command. To tell Access to search the entire table instead, choose Current Document from the Look In drop-down list, as shown in Figure 11-2.

Figure 11-2: To search the entire table, change Look In.

Match

Your options are as follows:

Any Part of Field: Allows a match anywhere in a field (finding Richard, Ulrich, and Lifestyles of the Rich and Famous). This is the default.

Whole Field: This requires that the search terms (what you type in the Find What box) be the entirety of the field value. So Rich won’t find Ulrich, Richlieu, Richard, or Richmond. It only finds Rich.

Start of Field: Recognizes only those matches that start from the beginning of the field. So Rich finds Richmond, but not Ulrich.

This option allows you to put in just part of a name, too, especially if you know only the beginning of a name or the start of an address.

To change the Match setting, click the down arrow next to the Match field (see Figure 11-3) and then make your choice from the drop-down menu that appears.

Figure 11-3: Using the Match option.

Search

If you’re finding too many matches, try limiting your search to one particular portion of the table with the help of the Search option. Search tells the Find command to look either

At all the records in the table (the default setting)

or

Up or down from the current record

Clicking a record halfway through the table and then telling Access to search Down from there confines your search to the bottom part of the table.

Fine-tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate offering from the drop-down menu.

Match Case

Checking the Match Case check box makes sure that the term you search for is exactly the same as the value stored in the database, including the same uppercase and lowercase characters.

This works really well if you’re searching for a name, rather than just a word, so that rich custard topping is not found when you search for (capital-R) Rich in the entire table.

Search Fields as Formatted

This option instructs Access to look at the formatted version of the field instead of the actual data you typed.

Limiting the search in this way is handy when you’re searching for dates, stock-keeping unit IDs, or any other field with quite a bit of specialized formatting.

Turn on Search Fields as Formatted by clicking the check box next to it.

This setting doesn’t work with Match Case, so if Match Case is checked, Search Fields as Formatted appears dimmed. In that case, uncheck Match Case to bring back the Search Fields as Formatted check box.

Most of the time, this option doesn’t make much difference. In fact, the only time you probably care about this particular Find option is when (or if) you search many highly formatted fields.

Sorting from A to Z or Z to A

Very few databases are organized into nice, convenient alphabetical lists. You don’t enter your records alphabetically; you enter them in the order they come to you. So what do you do when you need a list of products in product-number order or a list of addresses in zip code order right now?

Sorting by a single field

The solution lies in the sort commands, which are incredibly easy to use. The sort commands are on the Ribbon’s Home tab, in the Sort & Filter section. The two buttons (Ascending and Descending) do the job quite well:

Sort Ascending sorts your records from top to bottom:

• Records that begin with A are at the beginning, and records that begin with Z are at the end.

• If your field contains numeric data (such as zip codes and prices), an Ascending sort puts them in order from lowest number to highest.

Sort Descending sorts your records from bottom to top:

• Records that begin with Z are at the top, and records that begin with A are at the bottom of the list.

• If your field contains numeric data, a Descending sort puts the records in order from highest number to lowest.

Sorting on more than one field

What if you want to sort by zip code, and then within that sort, you want all the people with the same zip code to appear in Last Name order?

You can sort by more than one column at a time like this:

1. Click the heading of the first column to sort by.

The entire column is highlighted.

2. Hold down the Shift key and click the heading of the last column to sort by.

All columns from the first one to the last one are highlighted.

3. Choose either Sort Ascending or Sort Descending.

The sort is always performed from left to right.

In other words, you can’t sort by the contents of the fourth column and within that by the contents of the third column.

Sorting has its own peculiarity when working with numbers in a text field. When sorting a field that has numbers mixed in with spaces and letters (such as street addresses), Access ranks the numbers as though they were letters, not numbers. This behavior means that Access puts (say) “1065 W. Orange Street” before “129 Mulberry Street.” (Thanks to the peculiar way that your computer sorts information, the 0 in the second position of 1065 comes before the 2 in the second position of 129.)

Fast and Furious Filtering

Sometimes you need to see a group of records that share a common value in one field. Perhaps they all list a particular city, a certain job title, or they’re all products that have the same cost. Always willing to help, Access includes a special tool for this very purpose: the Filter command.

Filter uses your criteria and displays all matching records, creating a mini-table of only the records that meet your requirements. It’s like an instant query without all the work and planning. Of course, it’s not as flexible or powerful as a query, but it’s all you need when you’re looking for a fairly simple answer.

The Filter tool appears in the Sort & Filter section of the Ribbon’s Home tab, and you have the following choices for a simple filter:

Filter

Selection

Advanced Filter by Form

Advanced Filter/Sort

Toggle Filter

Each type of filter performs the same basic function, but in a slightly different way. The following sections cover the first three options. The Advanced Filter/Sort option, found by clicking the Advanced button, opens a window that actually has you building a query — selecting tables and fields to filter, setting up criteria for the filter to use while it’s finding specific records, that sort of thing. You can read about queries and familiarize yourself with the Advanced Filter/Sort tool in Chapter 12.

Filters work in tables, forms, and queries. Although you can apply a filter to a report, filtering reports can be a daunting task, one that I’m not going to get involved with here. Of course, what you read here can be applied to that process, should you want to try it on your own. And in the following sections, what you learn to apply to a table can also be applied when you’re working with queries and forms.

Filtering by a field’s content

The main Filter command enables you to filter your records so you view only records that meet specific criteria. Suppose, for example, that you want to see all records where employees work in the Sales Department. Here’s how to do it:

1. In your table of interest, click the small triangle on the field name for the field you want to filter (City in this case).

Access displays a pop-up menu like the one in Figure 11-4.

Don’t right-click the header at the top of the column (where it says City in the figure). Right-clicking there displays a different pop-up menu filled with wonderful things you can do to that column of your table.

Figure 11-4: Filtering a single field based on that field’s entries.

2. If you want to omit some entries from your filter, remove their check marks in the pop-up menu.

You can either

• Remove check marks from individual entries.

or

• Remove the Select All check mark to uncheck all the items.

Uncheck everything so you can easily check only those entries whose matching records you want to see. With all the entries checked, you see all the records.

3. Place check marks next to those entries you want to use in constructing your filter for the field.

Access searches the selected field and displays only those records that meet your Filter criteria.

4. Click OK.

All the records meeting the criteria set (by virtue of the items you checked) are displayed. This might be several records, a whole lot of records, or just one.

To jump back and see all of the original (unfiltered) records, you can either

Click the Toggle Filter button in the Sort & Filter section of the Ribbon.

or

Click the field name’s tiny triangle again and choose Clear Filter From Field Name.

The entire table, full of records, returns to view.

Filter by selection

The Selection command is the easiest of the Sort & Filter commands to use. It assumes that you’ve found one record that matches your criteria. Using the Selection filter is a lot like grabbing someone in a crowd and shouting: “Okay, everybody who’s like this guy here, line up over there.”

For example, imagine you want to find all the volunteers who live in Elizabethtown. You can use Selection filter in this manner:

1. Click the field that has the information you want to match.

In this case, it’s the City field.

2. Scroll through the list until you find the field entry that will serve as an appropriate example for your filter.

3. Click to select the value you’re searching for, right-click the cell containing the selection, and then choose Equals Whatever. (In this instance, it would be Equals London, as shown in Figure 11-5.) You can also select the cell and then click the Selection button on the Home tab (in the Sort & Filter section) to access the same list of choices.

Access immediately displays a table containing only the records matching your selection. (Again, check out Figure 11-5, where my City filter has been applied.)

4. Click the Toggle Filter button on the toolbar after you finish using the filter.

Your table or form returns to its regular display.

Figure 11-5: Access shows only those records matching the Filter by Selection criterion.

At this stage of the game, you may want to save a list of everything that matches your filter. Unfortunately, the Filter’s simplicity and ease of use now come back to haunt you. To permanently record your filtered search, you have to create a query. (See Chapter 12 for details about creating queries.)

You can also use the Filter menu that shows sorting options and the list of checked field values in the Filtering by a field’s content section earlier in this chapter. When you display the pop-up menu, unclick Select All and then check the value you want to look for. Then choose Text Filters from that same pop-up menu. The same list of options (Equals, Does Not Equal, Begins With, and so on) appears as a fly-out menu, and you can make your choice there.

Filter by Form

You can tighten a search by using additional filters to weed out undesirable matches, but that takes a ton of extra effort. For an easier way to isolate a group of records according to the values in more than one field, try Filter by Form.

Filter by Form uses more than one criterion to sift through records. In some ways, it’s like a simple query. (What’s a query? See Chapter 12.) It’s so similar to a query that you can even save your Filter by Form criteria as a full-fledged query!

Suppose, for example, that you need a list of all the employees at your company who work in a certain department and have a particular title. You can perform two Selection filters (on the Department and Job Title fields, using the employee database as an example) and write down the results of each to get your list, or you can do just one search with Filter by Form and see all the records that meet your criteria (based on their entries in multiple tables) in a single step.

To use Filter by Form, follow these steps:

1. On the Ribbon’s Home tab, click the Advanced button in the Sort & Filter section.

A menu appears.

2. Choose Filter by Form from the menu.

The table is replaced by a single row of cells, one under each field header in your table, as shown in Figure 11-6.

3. Click the first column that you want to filter.

Use the scroll bars to bring the column onscreen if it’s off to the right and can’t be seen.

The down arrow jumps to the column you click.

• Normally, Access shows a down-arrow button next to the first field in the table.

• If you previously used a Filter command with the table, Access puts the down-arrow button in the last field you filtered. (Again, see Figure 11-6.)

Figure 11-6: Filter by Form offers a grid and drop-down lists to set criteria for each field.

4. Click the down arrow to see a list of values that the field contains, as shown in Figure 11-7.

Figure 11-7: The drop-down list shows all unique values in this field.

5. In the list of values, click the value that you want to use in this Filter.

For instance, if you select Barcelona from the drop-down list in the City field, “Barcelona” moves into the City column. Access adds the quotes automatically — one less detail that you have to remember!

6. To add another filter option for the chosen field, click the Or tab in the lower-left corner of the table.

A new Filter by Form window appears, letting you add an alternate search condition. Access also offers an Or tab to the lower-left corner of the display, as shown in Figure 11-8.

The Filter by Form command likes to answer simple questions, such as “Show me all the customers who are Sales Associates.” It also provides answers to more complex questions like “Show me all the records containing Sales Associate or Sales Agent in the Title field and who have London in the City field,” and it performs both tasks easily.

Asking a more complex question (such as “Show me all the customers in either London or Barcelona who are Sales Agents and have no fax number”) requires a query. To find out about queries, flip ahead to Chapter 12.

7. For each additional field you want to filter, repeat Steps 3 through 6.

In this example, the second field to be filtered is Status, and Active is selected from the drop-down list.

8. When you finish entering all the criteria for the filter, click the Toggle Filter button.

Figure 11-9 shows the results.

Figure 11-8: Click the Or tab to further define the criteria.

Figure 11-9: Access finds all Sales Agents from the city of London — one record, in this case.

A final thought about Filter by Form:

Although you can get fancy by adding Or searches to your heart’s content, keeping track of your creation gets tough in no time at all. Before you go too far, remind yourself that queries work better than filters when the questions get complex. Flip to Chapter 12 for the lowdown on queries.

When you finish fiddling with your filter, click the Toggle Filter button. At that point, your table returns to normal (or at least as normal as data tables ever get).

Unfiltering in a form

What do you do when you enter criteria by mistake? Or when you decide that you really don’t want to include London in your filter right after you click London? No problem — the Clear Grid command comes to the rescue!

When you click the Clear Grid command (found in the Sort & Filter section’s Advanced menu), Access clears all the entries in the Filter by Form grid and gives you a nice, clean place to start over again.

Filter by excluding selection

The Selection filter can also be used to exclude certain records. This works great for times when you want to briefly hide a bunch of records that all share a unique attribute (a particular Postal Code, a certain state, a particular price, and so on). In the following sample, I searched for all sales-related titles in London (Sales Agent, Sales Representative, and Sales Manager). After seeing my results, I realized I didn’t want any sales managers.

Here’s how to make the Selection filter exclude records for you:

1. Scroll through the table until you find the value you want to exclude.

2. Right-click the field containing the value and then choose Does Not Equal ______ (where the blank represents the value you’ve right-clicked) from the menu that appears, as shown in Figure 11-10.

Those records matching the value you’ve chosen get out of the way so you can concentrate on the records that really interest you.

Figure 11-10: With one click, Access hides all Sales Managers who are from London.

You can also click the Selection button (in the Sort & Filter section of the Home tab) and choose Does Not Equal from the menu there. You’ll also notice the Contains and Does Not Contain commands. These are handy for culling records that have something in common, such as a particular word or number within them.