Create Multiple Indexes using an SQL (Access 2003)

Topic

New Reply

I’m attempting to create a table and indexes against certain fields in the table with the following.

‘Create T_XStatB
CurrentDb.Execute “CREATE TABLE T_XStatBX ( ” & _
“Seq INTEGER, ” & _
“Val REAL, ” & _
“FlagTw REAL, ” & _
“Slope REAL, ” & _
“SlopeAbs REAL, ” & _
“Crossover INTEGER);”

‘Create Indexes
CurrentDb.Execute “CREATE INDEX StatBIndex ” & _
“ON T_XStatBX (Val,FlagTW);”

I have no problem creating the table and fields in the table along with a single field index, but when I try to include multiple field indexes using a single SQL (i.e. (Val,FlagTW)), it doesn’t work. Am I doing something wrong?

Thanks in advance for any help!
Drew

Reply | Quote

Replies

The SQL looks OK. When I run it in my test database, it *does* create a composite index on two fields. Do you get an error message, and if so, what does it say?

Reply | Quote

I don’t get an error message. When I ‘Design’ on the table that was created (T_XStatBX), the ‘Indexed’ field/property indicates ‘No’.

Thanks,
Drew

Reply | Quote

For composite indexes, you must look at the Indexes window: select View | Indexes. The individual fields aren’t indexed.

If you wanted to create separate indexes on Val and on FlagTw, you should use two separate CREATE INDEX statements, using a different index name for each.

Reply | Quote

Ahhhhh…Thank you
What’s the difference between having composite verses separate indexes? I will be running multiple SQL’s against this table and the two field (Val and FlagTw) will be used in my criteria. I would like this to be as efficient as possible.

Regards,
Drew

Reply | Quote

A composite index means that the Jet Engine keeps track of pairs (or triples, or …) of values (one from FieldA, the other from FieldB), instead of values from a single field.
In particular, if you create a unique composite index, the values from each field may contain duplicates, but the combinations must be unique. For example, you could have

FieldA	FieldB
1	4
1	6
1	13
2	1
2	4
2	7
3	6
3	9
3	13

This kind of index is often used in the join table of a many-to-many relationship. Say you have students and classes. Each student can attend several classes, and many students can attend the same class, but the combination of a student and a class should be unique: a student cannot attend the same class twice at the same time.

Reply | Quote