Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Q about Multiple Indexes (Access 2000 >)

    Hi,
    I understand that indexes are useful for fields that are often searched or filtered on. They speed up the process in the table contains thousands of records. My Q is regarding multiple indexes...I just want some clarity regarding this issue:
    If I create a simple table with a primary key...that is naturally indexed (due to the key!) The table records are now autosorted due to the key value because of the index. Then I create another index (dupl. OK) on a text field...say Surname.

    The indexes are displayed in the indexes window in table design view. I notice that the order of the indexes have no play on the table sort order. If I drag the Surname index above the P/K...should this not now keep the surnames in a sort order??

    Am I clear enough...do you understand my query?

    Tx
    Regards,
    Rudi

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

    Re: Q about Multiple Indexes (Access 2000 >)

    The default sort order of a table is always that of the Primary Key (if there is one), regardless of the position of the primary key in the list.
    The other indexes are used to speed up sorting and searching.
    Warning: each index takes up space. Don't go overboard creating indexes.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Q about Multiple Indexes (Access 2000 >)

    The Indexes list in table design is presented alphabetically by Index name. The order in which the indexes appear in this list has absolutely no bearing on your table. When you first open a table in datasheet view, in will display in order by the PK (the default). You can change the order you view it by right-clicking on a column and selecting Ascending or Descending.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Q about Multiple Indexes (Access 2000 >)

    I hope you dont mind taking this further. If the table does not have a P/K, then the first index I create takes the preference in the table sort. When I create another that one is used in the sort order. I cannot seem to get the sort order back to the first index.??? (Unless I delete the index)

    Anyway....The P/K is the preference if there is one. If there is no P/K ... who cares what the order...the purpose of the indexes is more internal for the DB?? Is this what you are saying!!

    PS: I am aware of the negative aspects of multiple indexes.

    Cheers
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Q about Multiple Indexes (Access 2000 >)

    Tx Mark...that actually answers my Q quite clearly! I never realised it keeps an alphabetical order to the index name...how could I have missed that???
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Q about Multiple Indexes (Access 2000 >)

    One more comment :
    If you have the AutoIndex in the Tools/Option on, you can easily end up with duplicated indexes for a field.
    IMO, you should always disable this "feature".
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Q about Multiple Indexes (Access 2000 >)

    Hi Francois,
    Thanx for that tip. I am on a PC running Access 2000 currently. I cannot find AutoIndexes in the Options dialog. Is this feature only available on XP onwards?
    Tx
    Regards,
    Rudi

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

    Re: Q about Multiple Indexes (Access 2000 >)

    No, it's been there in all versions since Access 95 at least. It's in the Tables/Queries tab. See screenshot.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Q about Multiple Indexes (Access 2000 >)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    I'm looking for a checkbox!

    While on the subject...If no P/K was assigned, and there is no autonumber field...will access look for field names such as in thge autoindex textbox and attempt to P/K (or Index No Dupl's) this field...???

    Tx
    Regards,
    Rudi

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

    Re: Q about Multiple Indexes (Access 2000 >)

    When you import a table from an external file, or create a new field in a table, Access checks if the field name begins or ends with one of the strings in the AutoIndex box, and if so, it creates a non-unique index on that field. It doesn't matter whether the table has a primary key or not.
    When you create a new table and don't specify a primary key, Access will ask you whether it should create one when you save the table for the first time. Apart from that, it never automatically creates a unique index or primary key, as far as I know.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Q about Multiple Indexes (Access 2000 >)

    This has nothing to do with the primary key.
    Here a sample how to get a duplicated index.
    Let's say you have the default ID;Key;Code;Num in the autoindex in the options.
    Create a new table.
    Create a field with the name IDCustomer.
    Set this field as the Primary Key.
    Open the index window.
    You will see two indexes on the same field.
    One named PrimaryKey indexed on IDCustomer with Primary and Unique property set to yes and Ignore Nulls set to No.
    The second will be named IDCustomer, also indexed on IDCustomer and Primary, Unique and Ignore Nulls set to No.
    This are two indexes on the same field, wasting space and speed.
    Francois

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Q about Multiple Indexes (Access 2000 >)

    OK. Thank you both.. Hans and Francois for the warning and explanation. This is a useful tip I learned and an answer to my Q.
    Cheers
    Regards,
    Rudi

Posting Permissions

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