14,534
The more data you include in your tables the more you will need to have indexes to search and sort that data. However, there is a balance between having enough indexes and too many. Too many indexes will slow down the speed of updates on your records.
Access presets a number of Indexes for you. If you look in Tools ... Options under the Tables/Queries tab you will see that under "Auto Index on Import/Create" there is a list as follows:
ID;key;code;num
This means that Access will automatically create an index on any field which begins or ends with these terms. Although this can be helpful it can also mean that access will create duplicate indexes or indexes on fields you didn't intend to be indexed. If you are confident of setting your own indexes you can delete this list.
When Access creates an Index it essentially stores internally the value in the field(s) being indexed and the location of every field with that value. This then speeds up any search on that field.
When you set a field as a primary key it is automatically indexed. Most indexes you need to define will contain values from only a single field.
Creating an Index
To create an index on a single field, open the table in design view. Select the field on which you want to create an index. Go to the Indexed property and change the property value to Yes (Duplicates OK). The other value of Yes (No Duplicates) is only used on fields where the value is unique (no value is duplicated). The Yes (No duplicates) value is used by primary keys and is also useful when creating an index on foreign keys where the relationship to the other table is one to one only.
If you create an index on more than one field in a table for example two fields and set criteria on both fields in a query Access will combine the indexes to search the table.
Multiple Field Indexes
If you frequently set criteria on multiple fields when querying large tables it might be useful to create a multiple field index to save Access from combining multiple single field indexes. A good example of this is when you search on both surname and firstname fields.
To create a multiple field index, open the table in design view and open the index window (View ... Indexes). Under Index Name give a name to the multiple field index e.g. FullName. You only need to enter this on the first line. Under Field Name add the Surname field and set sort order. Then go to the next line and add the Firstname field under Field Name and set the sort order. You have now created a multiple field index.
