what is index?
- Thread starter Harrold
- Start date
- Local time
- Today, 14:46
- Joined
- Feb 28, 2001
- Messages
- 29,999
An index in MS Access is a data structure related to the contents of a table. You pick a field in the table and build an index on that field. Later, when you run something like a query that is based on sorting the indexed field, the index helps you do that action.
The index can be in ascending or descending order. The filed can be any data type that can be sorted reasonably. An index can span more than one field (but the fields must be in the same table).
Sometimes an index can be used to create what is called a constraint. For instance, you can define an index to be unique by defining it to allow No Dups (no duplicates). This is a constraint that says each value of the field must different from any other value for that field in the same table. Access enforces the constraint if you define the index in that particular way.
The index can be in ascending or descending order. The filed can be any data type that can be sorted reasonably. An index can span more than one field (but the fields must be in the same table).
Sometimes an index can be used to create what is called a constraint. For instance, you can define an index to be unique by defining it to allow No Dups (no duplicates). This is a constraint that says each value of the field must different from any other value for that field in the same table. Access enforces the constraint if you define the index in that particular way.
- Local time
- Tomorrow, 05:46
- Joined
- Jan 20, 2009
- Messages
- 12,895
BTW. A Primary Key is really just a designated index with Duplicates and Nulls disallowed.
Have you looked at the Indexes window?
In Access 2007 open a table in Design View and click Indexes under the Design Ribbon. I can't remember how to do it in older versions.
Have you looked at the Indexes window?
In Access 2007 open a table in Design View and click Indexes under the Design Ribbon. I can't remember how to do it in older versions.
- Local time
- Today, 20:46
- Joined
- Sep 12, 2006
- Messages
- 15,980
the purpose of an index is to allow fast access to very large tables
if you have an indexed field, then access can directly search the index for a particular value - because the nidex will be sorted. if you don't it has to examine every record indivdually to determine whether it meets your requirements.
So index any fields which you expect to use regularly for searching.
Note that Access maintains a hidden index anyway, for the many side of a table included in a relationship - without oyu needing to specifically create one.
if you have an indexed field, then access can directly search the index for a particular value - because the nidex will be sorted. if you don't it has to examine every record indivdually to determine whether it meets your requirements.
So index any fields which you expect to use regularly for searching.
Note that Access maintains a hidden index anyway, for the many side of a table included in a relationship - without oyu needing to specifically create one.
Similar threads
