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.
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.
Unique number given each record
Last name of employee
First name of employee
Address of employee
City of employee
State of employee
Zip code of employee
Date of hire
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.
Date of Hire
123 Main St.
478 Creek View Dr.
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.