Absolute Beginners Guide to Microsoft Office Access 2003

So far, you've dealt with only comparatively small amounts of information in your database. When you're looking over a list of five seed companies, you can easily find the one you want. But what happens when you have thousands of rows of data in one of your tables and need to find something? Fortunately, Access offers several ways to find data. We'll introduce a few of these ways in this chapter, and you'll see more later in the book:

  • Sorting datasheets

  • Using Filter by Selection

  • Using the Find dialog box

(Chapter 8, "Creating and Using Data Entry Forms," shows you how to search for data in a form.)

Sorting in a Datasheet

Sometimes, you can find information just by scanning down a list, provided that the list is sorted in the correct order. Access enables you to sort a datasheet according to the data in any column. Follow these steps to see how datasheet sorting works in Access:

  1. Open the Catalogs table in datasheet view. By default, the records will be sorted by their primary keys, in alphabetical order.

  2. Click anywhere in the Address column, and then click the Sort Ascending button on the Access toolbar (it's the one with the A above the Z and a down arrow next to them). Now the records are sorted by address. Note that all the other columns are rearranged at the same time as the address column; Access doesn't sort just one column because this would break up records.

  3. Click anywhere in the City column and select Records, Sort, Sort Ascending to sort the records by city.

  4. Right-click in the State column and select Sort Descending to sort the records in descending order by state.

  5. Click the Close button to close the table. Access asks whether to save changes to the design of the table. If you click Yes, the last sort you applied will be reused the next time you open the table. Click No this time.

As you can see, this is another area where Access offers you multiple ways to perform the same task.

Using Filter by Selection

Sometimes, the easiest way to find what you're looking for is to narrow the search. Access provides a feature called Filter by Selection for those times. Here's how it works:

  1. Open the Catalogs table in datasheet view.

  2. Click in the Country field, on one of the rows whose country is USA.

  3. Right-click and select Filter by Selection . The datasheet changes to show only records whose country is USA. The navigation bar shows the text Filtered in the navigational toolbar to let you know that some records might be missing (see Figure 5.17).

    Figure 5.17. A filtered datasheet.

  4. Right-click in the field again and select Remove Filter/Sort to see all the records in the datasheet.

  5. Right-click in the field again and select Filter Excluding Selection . This time, the datasheet shows only records that do not match the selected value.

  6. Remove the filter and close the datasheet.

    You can also invoke Filter by Selection from the Records, Filter menu or from buttons on the table datasheet toolbar.

Using the Find Dialog Box

For maximum flexibility in locating data, Access also supports a standard Find and Replace dialog box. To invoke this dialog box, shown in Figure 5.18, open a datasheet, select any cell in the field you want to search and then press Ctrl+F.

Figure 5.18. The Find and Replace dialog box.

Type the data you want to find in the Find What combo box. If you've recently searched for something, you can repeat the search by selecting the data from the drop-down list in this combo box.

The Look In box gives you the choice between searching the field in which the cursor is positioned (in this case, Name ) or the entire table.

The Match box lets you choose whether the data you've entered needs to match the whole field, any part of the field, or characters at the start of the field to be considered a match.

The Search box lets you choose whether to search up from the current cursor position, down from the current cursor position, or through the entire table.

You can also use the check boxes to make the search case sensitive or to search the data as it's presented rather than as it's stored. Access has the capability to format data onscreen differently from how it stores it in the table (see Chapter 11 for more details).

Tip

Access also supports an even more flexible filtering mode called Filter by Form, in which you can select the values by which to filter without needing to find them first. You'll learn about Filter by Form in Chapter 8.

Tip

To change the data after you find it, select the Replace tab in the Find and Replace dialog box and enter the new value in the control that appears. Access prompts you before making any data changes.

When you've entered all your options, click the Find Next button (or press Alt+F ). Access finds the next matching data in the table and either highlights it onscreen for you or displays an error message if it was unable to find any matching data.

The Absolute Minimum

In this chapter you learned the following:

  • You can create Access tables by typing data in a datasheet, by using the Table Wizard, or by working in table design view.

  • How to add smart tags at the table level.

  • You can enter data by typing it in the datasheet. Access offers a rich set of controls for formatting and moving around in datasheets.

  • You can use the Sorting, Filter by Selection, or Find and Replace capabilities of Access to quickly find data, even in very large tables.

Related

Категории