Indexing in Access 2010 is very beneficial especially when you are dealing with huge database. It saves the location of records based on the fields that you choose to index. Once Access has the location from the index, then it can retrieve the data by moving directly to the correct location. In this way, using an index can be considerably faster than scanning through all the records to find the data or particular record. This post will elaborate the usage of indexing.
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.
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.
Now you can see the data types of the respective fields and apply different rules and constraints.
Switch to Design tab, and click Indexes button.
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.
Now specify appropriate name of the index, corresponding to the Field Name.
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.
We have added indexes to speed up the search. You can also add indexes for other fields and tables by following the above procedure.
