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:
Select the field to be indexed. Click the Indexed row of the Field Properties pane. 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:
Choose View | Indexes. The Indexes window appears. Type the index name in the Index Name column. From the Field Name column, select the desired fields to include in the index. Select the desired index properties (see Figure 14.10).
Click OK to close the Indexes dialog box.
 | Selecting Yes (No Duplicates) causes duplicate values to not be permitted in the field that is being indexed. |
Task: Combining Your Skills
 | 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: |
Not using a wizard, create a new database called MYDB. 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|
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? |
Set a primary key on the EmpID field. Save the table as tblEmployees. Add to the new table the records shown in Table 14.5.
Table 14.5. Records for the tblEmployees TableLast 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 |
Add appropriate captions for the EmpID, LastName, FirstName, DateofHire, and HourlyRate fields. 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. 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. 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. Create separate indexes for the FirstName, City, State, HourlyRate, and DateofHire fields. Create a compound index that includes the LastName and FirstName fields. Call the index FullName.
 |
