Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Multiple Indexes using an SQL (Access 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Multiple Indexes using an SQL (Access 2003)

    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?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Indexes using an SQL (Access 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Multiple Indexes using an SQL (Access 2003)

    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.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Indexes using an SQL (Access 2003)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Multiple Indexes using an SQL (Access 2003)

    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

    <table border=1><td>FieldA</td><td>FieldB</td><td align=right>1</td><td align=right>4</td><td align=right>1</td><td align=right>6</td><td align=right>1</td><td align=right>13</td><td align=right>2</td><td align=right>1</td><td align=right>2</td><td align=right>4</td><td align=right>2</td><td align=right>7</td><td align=right>3</td><td align=right>6</td><td align=right>3</td><td align=right>9</td><td align=right>3</td><td align=right>13</td></table>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •