Microsoft Access is a database management program which stores data in the form of tables. Often the database contains a very large amount of data and you can imagine the problem you will have if you have to go through each and every record in your database table in order to get some specific records. Quit a cumbersome process! To get over this problem, MS Access uses Indexing. Indexing makes searching, sorting and locating information easier and faster.

Let’s say we wish to read a certain chapter from a book. Do we go through each and every page of the book in order to go to the specific chapter? No, we simply open the book, look for the page number of the chapter we wish to read and directly go to that page. Similarly, in MS Access, an index is used to store the location of the records based on one or more fields that we choose to index. The Access retrieves the data quickly from its location stored in the index just as we have seen in the book example.

Deciding the field to base your index on is a process which requires a little attention. If your choose to index your database table on a more common field such as address or name and you have only single field index, you will get surrounded with a lot of data that you don’t actually need. Besides it will slow up the searching process. So, you should have at least one index based on a field such as telephone number or ID which is unique for your database table. This unique field is also known as the Primary key in MS Access.

You can index your database table either on single field or multiple fields in MS. Access. Let’s explore both the techniques one by one:

Step by step instructions for creating Single-Field Index:

Step 1: Open your database.

Step 2: Choose the table for which you want to create index by right clicking on the navigation pane. Choose Design from View menu to open it in Design View.

Step 3: Select the field you want your table index to be based on and click on the field. An Indexed Property box will open. In this box, choose Duplicates OK or No Duplicates.

Step 4: Save your results by clicking on Save icon on the title bar or simply press ctrl+s.

Now we will see process of creating Multiple-Field index in MS Access.

Step by step instructions for creating Multiple-Field Index:

Step 1: Open your database.

Step 2: Choose the table for which you want to create index by right clicking on the navigation pane. Choose Design from View menu to open it in Design View.

Step 3: Click Indexes on the Design tab in the Show/Hide group on the Design tab

In the Index Name column, type a name for the index in the Field Name column and click the arrow. Select the first field for the index.

Step 4: Repeat the process to select as many fields as you want to include in the index. You can change the sort order by clicking on Ascending or Descending.

In the index properties in index window, set the field property to Primary, Unique or Ignore nulls.

Step 5:Click save to save your changes.