Multi-field indexes

B

Brendan Reynolds

John said:
Hi

Is there a way to create a multi-field index?
Click to expand...


In Access 2007, with a table open in design view, there's an Indexes icon on
the ribbon. In earlier versions, there's an Indexes icon on the Table Design
toolbar, or you can choose Indexes from the View menu when in table design
view. Once you have the Indexes window open, to create a multi-field index,
enter a name for the index in the first column (Index Name), and choose a
field in the second column (Field Name). Then go down one row, leave the
first column blank, and in the second column choose the second field to be
added to the index. All rows with no entry in the Index Name column form
part of the same index. Each index can include up to ten fields.
 
B

Brendan Reynolds

John said:
Hi

Many thanks. How can I use this index in an sql query?
Click to expand...


The database engine, not the developer, determines which indexes to use when
executing queries.
 
J

John W. Vinson

Hi

Many thanks. How can I use this index in an sql query?
Click to expand...

You don't need to explicitly "use" it. If your query has criteria on the
fields in the index (all of them, or the first field, or the first two
fields...), or sorts by them, the query optimizer will make use of the index
with no need for you to tell it to do so.
 
Top