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: (1) enter matching data, or (2) 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 next To Do exercise helps 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.

To Do: Set Up and Use a Criteria Form with Matching Data
  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 21.7. You should see field names, field text boxes, the Criteria indicator in the upper-right corner, criteria form buttons, and navigation buttons.

    Figure 21.7. The criteria form.

    graphics/21fig07.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 sound, which indicates that 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)

  • < (less than)

  • >= (greater than or equal to)

  • <= (less 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 To Do exercise, you create a criteria record using the data form to search for all employee ID numbers that are greater than 670.

To Do: Use Comparison Operators for Criteria
  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 21.8). Entering the greater than symbol and 670 tells Excel to find any entry whose employee ID number is greater than 670.

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

    graphics/21fig08.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.

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

    graphics/bookpencil_icon.gif

    Notice that Excel finds the matching records in the order in which they appear in the rows in the worksheet, even though the number 672 comes before 680. If you sort the data according to employee ID number, Excel would find the ID numbers greater than 670 in numeric order. Hour 22, "Working with Your Database," teaches you how to sort data in a database.


  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 six types of criteria:

  • All? Displays all records in the field.

  • Top Ten? Shows up to 10 records from the top or bottom of the list 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 only when a column contains blank fields.

  • Nonblanks? Displays all records with values that are not blanks in the field only when a column contains blank fields.

Perform the steps in the next To Do exercise 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.

graphics/lightbulb_icon.gif

Data stored to the left or right of the Excel database can be hidden when you filter the database. If other data shares the worksheet with the list, store the data in rows above or below the database area or on another sheet in the workbook.


To Do: Filter Data
  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.

    Remember, you can only filter one list at a time on a worksheet.

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

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

    graphics/21fig09.jpg

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

    graphics/lightbulb_icon.gif

    You can click the column heading and press Alt+Down Arrow to see a drop-down list of data you want to display.


  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.

    graphics/bookpencil_icon.gif

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




    Part I: Excel Basics
     
    ASPTreeView.com
     
    Evaluation has ёёС»ГУККЪАexpired.
    Info...