Multi-field indexes

[John]

Hi

Is there a way to create a multi-field index?

Thanks

Regards

 

[Brendan Reynolds]

Hi

Is there a way to create a multi-field index?

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.

 

[John]

Hi

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

Thanks

Regards

 

[Brendan Reynolds]

Hi

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

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

 

[John W. Vinson]

Hi

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

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.