Creating an Index in Microsoft Access 2013

Creating an Index in Microsoft Access 2013: Video

This video shows creating an index in Microsoft Access 2013.

Creating an Index in Microsoft Access 2013: Overview

When you are creating indexes, you want to try and use field values that will identify each record in your table as uniquely as possible. If you are a good database designer, there will already be a single field in your table that already does this: your primary key field. However, you can create additional indexes on other fields to use in queries for faster query processing.

To create an index, open up the table which you would like to index in table design view. Then click the “Indexes” button in the “Show/Hide” group on the “Design” tab of the “Table Tools” contextual tab to show the “Indexes” dialog box. Click into the next available row under the “Index Name” column, and type a name for your new index. To the right, click into the “Field Name” column, and select the name of the field within the table which you wish to index. To the right of that, select whether that field should be sorted in “Ascending” (A-Z, 1-9) or “Descending” (Z-A, 9-1) order.

In the “Index Properties” section at the bottom of the dialog box, you have three drop-down text boxes into which you can set the properties of the index. The first property is “Primary,” and can accept either a “Yes” or “No” value. Whatever index is the primary key of the table will show “Yes” in this property, and all others will show “No.” There can only be one primary key field in a table.

The next property, “Unique,” asks if the values within the field will always be unique (like the values in a primary key field are). Once again, you can select either “Yes” or “No,” as appropriate.

You can then also set the “Ignore Nulls” property to “Yes” or “No” to either include or exclude “Null” (empty) values from the sorting. Nulls occur when there has been no data entry in the field for a record. For example, if you skipped entering an address into a customer record, the address field would contain a “null” value. It is not equivalent to zero, as zero is still a value. “Null” is simply “unknown.”

When you have finished creating the index, you can close the “Indexes” dialog box. Then click the “Save” button in the Quick Access toolbar to save your structural modifications to the table.