MS-Access / Getting Started

Multiple-Field Indexes

If you often provide multiple criteria in searches against large tables, you might want to consider creating a few multiple-field indexes. This helps Access 2010 narrow the search quickly without having to match values from two separate indexes. For example, suppose you often perform a search for contacts by last name and first name. If you create an index that includes both of these fields, Access can satisfy your query more rapidly.

To create a multiple-field index, you must open the Table window in Design view and open the Indexes window by clicking the Indexes button in the Show/Hide group of the Design contextual tab on the ribbon. You can see the primary key index and the index that you defined on City in the previous section as well as the index defined by the Application Part (ZIP_PostalCode index on the ZIPPostal field). Each of these indexes comprises exactly one field.

To create a multiple-field index, move the insertion point to an empty row in the Indexes window and type a unique name. In this example, you want a multiple-field index using the Last Name and First Name fields, so FullName might be a reasonable index name. Select the Last Name field in the Field Name column of this row. To add the other field, skip down to the next row and select First Name without typing a new index name.

To insert a row in the middle of the list in the Indexes window, right-click in the Index Name column and then choose Insert Rows from the shortcut menu.

You can remove an existing single-field index by changing the Indexed property of a field to No on the field's property list. The only way to remove a multiple-field index is via the Indexes window. To remove a multiple-field index, select the rows (by holding down the Ctrl key as you click each row selector) that define the index and then press Delete. Access 2010 saves any index changes you make when you save the table definition.

Access 2010 can use a multiple-field index in a search even if you don't provide search values for all the fields, so long as you provide search criteria for consecutive fields starting with the first field. Therefore, with the FullName multiple-field index, you can search for last name or for last name and first name. Therefore, with the FullName multiple-field index, you can search for last name or for last name and first name. There's one additional limitation on when Access can use multiple-field indexes: Only the last search criterion you supply can be an inequality, such as >, >=, <, or <=. In other words, Access can use the index when you specify searches such as these:

Last Name = "Martin"
Last Name > "James"
Last Name = "Buchanan" And First Name = "Jordan"
Last Name = "Michel" And First Name >= "Bobby"

But Access will not use the FullName index, if you ask for

Last Name > "Warne" And First Name > "John"

because only the last field in the search (First Name) can be an inequality. Access also will not use this index if you ask for

First Name = "John"

because the first field of the multiple-field index (Last Name) is missing from the search criterion.

[Previous] [Contents] [Next]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations