[ Team LiB ] Previous Section Next Section

Using Indexes to Improve Performance

Indexes improve performance when you're searching, sorting, or grouping on a field or fields. Primary key indexes are used to maintain unique values for records. For example, you can create a single-field index that does not allow a duplicate order number or a multiple-field index that does not allow records with the same first and last names.

To create an index based on a single field (from Design view), follow these steps:

  1. Select the field to be indexed.

  2. Click the Indexed row of the Field Properties pane.

  3. Select the desired index type—No, Yes (Duplicates OK), or Yes (No Duplicates). The Yes (Duplicates OK) option means that you are creating an index and that you will allow duplicates within that field. The Yes (No Duplicates) option means that you are creating an index and you will not allow duplicate values within the index. If the index is based on company name and you select Yes (Duplicates OK), you can enter two companies with the same name. If you select Yes (No Duplicates), you cannot enter two companies with the same name.

To create an index based on multiple fields (from Design view), follow these steps:

  1. Choose View | Indexes. The Indexes window appears.

  2. Type the index name in the Index Name column.

  3. From the Field Name column, select the desired fields to include in the index.

  4. Select the desired index properties (see Figure 14.10).

    Figure 14.10. Creating a multifield index.

    graphics/14fig10.jpg

  5. Click OK to close the Indexes dialog box.

graphics/book_icon.gif

Selecting Yes (No Duplicates) causes duplicate values to not be permitted in the field that is being indexed.


Task: Combining Your Skills

graphics/todo_icon.gif

It's time to combine your skills. This task takes you through the process of creating a new database and then adding a table to it. It walks you through setting several properties of the table and even adding data to it:

  1. Not using a wizard, create a new database called MYDB.

  2. Create a new table, called tblEmployees, in Design view. The tblEmployees table should have the structure shown in Table 14.4.

    Table 14.4. The Structure of the tblEmployees Table

    Field Name

    Data Type

    Size

    Description

    EmpID

    Autonumber

    Long Integer

    Unique number given each record

    LastName

    Text

    25

    Last name of employee

    FirstName

    Text

    15

    First name of employee

    Address

    Text

    20

    Address of employee

    City

    Text

    15

    City of employee

    State

    Text

    2

    State of employee

    Zip

    Text

    5

    Zip code of employee

    Phone

    Text

    12

    Employee phone

    DateofHire

    Date

    N/A

    Date of hire

    HourlyRate

    Currency

    N/A

    Hourly rate

    Pension

    Yes/No

    N/A

    Does employee have a company pension?

  3. Set a primary key on the EmpID field.

  4. Save the table as tblEmployees.

  5. Add to the new table the records shown in Table 14.5.

    Table 14.5. Records for the tblEmployees Table

    Last Name

    First Name

    Address

    City

    State

    Zip

    Phone

    Date of Hire

    Hourly Rate

    Pension

    Forman

    Shell

    123 Main St.

    York

    PA

    17401

    717-755-8976

    1/1/1995

    $125.00

    Yes

    Terry

    Sue

    478 Creek View Dr.

    Camp Hill

    PA

    17011

    717-737-9087

    6/15/2000

    $180.00

    No

  6. Add appropriate captions for the EmpID, LastName, FirstName, DateofHire, and HourlyRate fields.

  7. Format the HourlyRate field as Currency. Provide a default value of $125.00. Add a validation rule to ensure that HourlyRate is between 0 and $250.00. Add appropriate validation text.

  8. Format the DateofHire field as Medium Date. Provide a default value of today's date. Add a validation rule to ensure that DateofHire is less than or equal to today's date. Add appropriate validation text.

  9. Use the Input Mask Wizard to place a phone number input mask on the Phone field. Depending on the input mask that you select, you can provide a different look and feel for the phone number.

  10. Create separate indexes for the FirstName, City, State, HourlyRate, and DateofHire fields.

  11. Create a compound index that includes the LastName and FirstName fields. Call the index FullName.

    [ Team LiB ] Previous Section Next Section