Searching for Data


After you enter data into the database, you can use the criteria form or Excel's AutoFilter feature to search for data. The criteria form lets you use comparison criteria in two ways to find records:

  • Enter matching data

or

  • Use comparison operators

Entering Matching Data Criteria

You can find specific records using a criteria form, which is a subset of the data form, to create a special criteria record. You enter a word, phrase, or value into the criteria record. This type of criteria is a comparison criteria.

You can also use the following wildcards, which are characters that represent information you don't know or information that is common to many records, when specifying criteria:

  • A question mark ( ? ) represents a single character.

  • An asterisk ( * ) represents multiple characters.

For example, you can use the ? wildcard to find everyone whose three-digit department code has 30 as the last two digits by typing ?30 . Or you can use the * wildcard to find everyone whose last name begins with a B by typing B* in the LNAME field.

After you create the criteria record, the Find Prev and Find Next buttons in the data form jump only to the record that matches the criteria.

The following steps help you set up a criteria record that uses the criteria form to search for specific data. You'll search for all last names that begin with a D.

  1. Select any cell in your database. This step selects a cell within the list you want to search.

  2. Choose Data, Form. Click the Criteria button. The criteria form pops up, which looks similar to the data form, as shown in Figure 50.7. You should see field names, field text boxes, the Criteria indicator in the upper-right corner, criteria form buttons, and navigation buttons.

    Figure 50.7. The criteria form.

    graphics/50fig07.jpg

  3. You want to use the * wildcard to search for all last names that begin with the letter D. Click the LName box and type D* . Entering * with the letter D tells Excel to find any entry whose last name starts with a D.

  4. To use the data form criteria record, click the Find Next button. The fourth record displays in the data form because the last name is Dreyfus.

  5. Click the Find Next button again. You hear a beep because no more records match the criteria.

Clearing Criteria

It's a good idea to clear the information from the criteria record when you're done finding the matching records. Otherwise, as you continue to use the data form, Excel uses the same criteria when you click the Find Prev and Find Next buttons.

To remove the criteria, click the Criteria button in the data form. You should see the criteria form. Click the Clear button. Excel removes all the information from the criteria record. If you want to restore the criteria, you can click the Restore button. Click the Close button to close the data form.

Using Comparison Operators

You can also search for a condition that must be evaluated, such as all records containing medical benefits less than $5,000. You can use the following comparison operators in Excel search criteria:

  • = (equal to)

  • > (greater than)

  • > = (greater than or equal to)

  • <> (not equal to)

To use a comparison operator to search for records containing medical benefits greater than $5,000, you would enter >5000 in the MedBene field in the criteria form.

In the following steps, you create a criteria record using the data form to search for all employee ID numbers that are greater than 670.

  1. Select any cell in your database. This step selects a cell within the list you want to search.

  2. Choose Data, Form. Click the Criteria button. The criteria form appears.

  3. In the EmpID box, type >670 (see Figure 50.8). Entering the greater than symbol and 670 tells Excel to find any entry whose employee ID number is greater than 670.

    Figure 50.8. Entering a comparison operator in the criteria form.

    graphics/50fig08.jpg

  4. To use the criteria record, click the Find Next button. The fourth record displays in the data form, showing the employee ID number 680 .

    graphics/bookpencil_icon.gif

    When you choose Data, Filter and a checkmark appears next to AutoFilter, select AutoFilter to turn it off before selecting another Excel database.


  5. Click the Find Next button again. The next record matching the criteria, record 5, displays. You should see the employee ID number 672 .

  6. Click the Find Next button again. You hear a beep because no more records match the criteria. You are done searching for data.

  7. Click the Clear button to clear the criteria in the form.

  8. Click the Close button to close the criteria form.

Using AutoFilter

Another way to search for data in a database is to use AutoFilter. This feature displays a subset of data without moving or sorting the data. Filtering data inserts drop-down arrows next to column headings in an Excel database. Selecting an item from a drop-down list hides all rows except rows that contain the selected value. You can edit and format the cells that are visible.

At certain times, you might want to work with a subset of data. For example, you might want to extract a partial list of data to give to someone who doesn't need the entire database list. Or maybe you want to use a filtered view of the data to create a report uncluttered by extraneous information. You can filter your data and move it somewhere else, such as to another worksheet, workbook, or application. At some point, you might want to delete unwanted records from the data. You can do so by filtering or extracting data from your list.

Filters enable you to display five types of criteria:

  • All Displays all records in the field.

  • Custom Opens the Custom AutoFilter dialog box so that you can create AND or OR criteria.

  • Exact Values Shows only records with this exact value in the field.

  • Blanks Shows all records with blanks in the field.

  • Nonblanks Displays all records with values that are not blanks in the field.

Perform the following steps to filter data and display specific data in your My Database file. First, you add a field name and field to the database, adding a column for the number of years an employee is with the company.

  1. Click cell F4 and type EmpPeriod . This entry is a field name for an employee's employment period with the company.

  2. Select cells F5:F10 and type 20 , 12 , 5 , 8 , 10 , 15 . Remember to use the Enter key to move to the next cell. Now, in column F, you have the length of employment for each employee.

  3. Click the Save button on the Standard toolbar to save the new data in your file.

  4. Select any cell in the list. This step selects a cell within the list you want to filter.

  5. Choose Data, Filter, AutoFilter. Excel displays drop-down arrow buttons next to each column heading in the database, as shown in Figure 50.9.

    Figure 50.9. AutoFilter arrow buttons next to the column headings.

    graphics/50fig09.gif

  6. Click the drop-down arrow for the Dept column. The drop-down list shows the unique values for the column.

  7. Select Sales. You should see two records, and the rest of the records are hidden. The blue arrow on the filter button indicates the filtered data is based on criteria you selected in the Dept column. The row header numbers for the filtered records also appear in blue.

  8. To show all the records again, choose Data, Filter, Show All. Now you should see all six records so that Excel can filter records based on all the records in the entire database.

  9. Now that you're finished filtering, shut off AutoFilter. Choose Data, Filter, AutoFilter. This step removes the drop-down arrow buttons from the column headings in the database, redisplays the hidden rows, and turns off the AutoFilter feature for this database.



Sams Teach Yourself Office Productivity All in One
Sams Teach Yourself Office Productivity All in One (Sams Teach Yourself All in One)
ISBN: 0672325349
EAN: 2147483647
Year: 2003
Pages: 474
Authors: Greg Perry

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net