471,873 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,873 developers and data experts.

Indexes in Microsoft Access

14,534 Expert Mod 8TB
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:


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.
Nov 28 '07 #1
0 7228

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by noelle.bond | last post: by
4 posts views Thread by WindAndWaves | last post: by
5 posts views Thread by Bruce Rusk | last post: by
2 posts views Thread by Lyle Fairfield | last post: by
2 posts views Thread by umair.cheema | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.