ACC2000: Data Not Sorted in Index Field Order

ID: Q208503


The information in this article applies to:

Novice: Requires knowledge of the user interface on single-user computers.


SYMPTOMS

When you use an index on a table without a primary index, the table does not sort according to the order of the fields in the index.


CAUSE

Opening a table in Datasheet view is, internally, the same as opening a recordset. If there is no primary index on the table, Microsoft Access uses any field with a unique index to sort the recordset. If none or more than one of the fields has a unique index, Microsoft Access chooses one at random. The order of the indexes shown in the Index window has no bearing on which index is used to sort the recordset.


RESOLUTION

To sort a recordset on a particular field, either make that field the primary index for the table (by setting its PrimaryIndex property to Yes), or use a query to sort the data.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access and open any database.


  2. Create a table with three fields. Make one of the fields an AutoNumber field, and make the other two Text fields.


  3. Do not specify a primary key for the table. Define an ascending compound index on the two text fields. For example:
    
       Index Name     Field Name     Sort Order
       ----------------------------------------
       FieldText      Field1         Ascending 
                      Field2         Ascending  
    Set all index properties to No.


  4. Save the table, and then enter the following data in the table:


  5. 
       AutoNumber  Field1  Field2
       --------------------------
       1           zzzz    dddd
       2           dddd    yyyy
       3           mmmm    iiii 
  6. Close the table, and then open it in Datasheet view. Note that it is sorted as follows:


  7. 
       AutoNumber  Field1  Field2
       --------------------------
       2           dddd    yyyy
       3           mmmm    iiii
       1           zzzz    dddd 
  8. Switch to Design view and add the AutoNumber field to the index:
    
       Index Name     Field Name     Sort Order
       ----------------------------------------
       AutoNumber     Autonumber     Ascending
       FieldText      Field1         Ascending
                      Field2         Ascending 
    Leave all the index properties set to No.


  9. Save and close the table, and then open it in Datasheet view. Note that it is sorted as follows:
    
       AutoNumber  Field1  Field2
       --------------------------
       2           dddd    yyyy
       3           mmmm    iiii
       1           zzzz    dddd 
    Instead of sorting first on the AutoNumber field, and then on Field1 and Field2 as you might expect, the sorting remains unchanged.


  10. Set the AutoNumber field's UniqueIndex property to Yes. The table is sorted as:


  11. 
       AutoNumber  Field1  Field2
       --------------------------
       1           zzzz    dddd
       2           dddd    yyyy
       3           mmmm    iiii 
    Note that the data is sorted on the AutoNumber field.


REFERENCES

For more information about indexes, click Microsoft Access Help on the Help menu, type "Create an index to find and sort records faster" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: indexes indexing sorting ordering prb


Keywords          : kbdta RptSort 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999