An index is a data structure, a special data structure designed to improve the speed of data retrieval. If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Access uses indexes in a table as you use an index in a book to find data.
Let us now look into what indexes Microsoft Access creates by default and how to create them ourselves and learn how to delete any unnecessary indexes. Open the tblEmployees table in Access database which we have created.
Let us now go to the File menu and select Options.
You will see the Access Options window.
Go to the Object Designers and you will see a section labeled AutoIndex on Import/Create and in the textbox you will see ID;key;code;num. By default, access automatically adds a secondary index to fields that start or end with these names and that goes for fields you have imported as well as ones you have manually created.
If you want to make any field indexed you can go to the Field tab.
Select any field that you want indexed and check the Indexed checkbox in Field Validation section. You also have alternate options for creating or removing an index. You can go back to the Design View.
You can adjust indexes by selecting any field. You can also see how they are indexed in the field properties area below. Any field that has No selected next to indexed, means there is no index for that given field. You can change that by clicking on the drop-down menu and choosing the other two options — Yes (Duplicate OK) and Yes (No duplicates).
The last option Yes (No Duplicates) means that Access will automatically prohibit duplicate values in that field. Let us now create an index for our last name field.
Let us select LastName to index and say Yes (Duplicates OK). As we save, Access will create that index. Another area where you can view and adjust your indexes for a table is the tables design area in the Show/Hide group.
If you click on this Indexes button that will bring up a special view displaying all the indexes created for this table.
We now have two indexes for tblEmployees —one that was created automatically based on the primary key field and one that we just created for the LastName field. These are the different ways to deal with indexes in Microsoft Access tables.