Create Table Index In Access 2010


Access 2010 offers a lot of options and features to speed up the search, so that users can easily find the required data from the data sets. One of the features which help database administrators search the required data is Index. The Index feature comes useful in situations where you often sort and search a table and want to find out the search results instantly.

Implementing the classic concept of indexing, Access 2010 saves the location of the data, so that it can be easily retrieved when required. The workability revolves around saving the location of data and fetching the results from the indexes. If your tables are properly indexed, it takes less time to search the specific record from the selected set of tables. When you create a table, Access 2010 automatically creates index to let you find the items from the table, while sometimes you need to create custom indexes to find a required type of data from the records. It provides an easy way of creating indexes for your data sets. In this post, we will explain how you can create and manage  indexes.

Launch Access 2010, create a table or open an existing table to apply indexing. For instance we have included a table in Access 2010 as shown in the figure.

table in simple layout

To start indexing we will switch to Design view. Right-click the table, click Design View or look out for small button at the bottom-right of the screen as shown in the screenshot below.

design view 1

Now you can see the data types of the respective fields and apply different rules and constraints.

design view of table 1

Switch to Design tab, and click Indexes button.

design tab indexes

You will reach Indexes dialog, Under Field Name column add fields for which you want to add indexes, we add three fields from AddictiveTips table; Course Code, Lecturer Id, and Addictive_ID as shown in the screenshot below.

Index names

Now specify appropriate name of the index, corresponding to the Field Name.

naming indexing

For changing index properties, select index name and under Index Properties change the desired properties. In Index Properties, there are three row labels; Primary, Unique, and ignore Nulls. Choose the options of corresponding properties very meticulously to avoid any conflict among fields.

Here we are specifying Lecturer Id indexing properties, we will choose No for Primary key, Yes for Unique ,and No for Ignore Nulls, these options enforce Lecturer Id field’s entries unique and not empty. After adding each index name, close the window.

indexing properties 1

We have added indexes to speed up the search. You can also add indexes for other fields and tables by following the above procedure.

  • Me

    What no comment on how indxes slow down data entry?

    Nothing about the need to test to see whether an index is actually beneficial?