Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208503 - Last Review: June 28, 2004 - Revision: 2.0
ACC2000: Data Not Sorted in Index Field Order
This article was previously published under Q208503
Novice: Requires knowledge of the user interface on single-user computers.
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.
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.
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.
Steps to Reproduce Behavior
- Start Microsoft Access and open any database.
- Create a table with three fields. Make one of the fields an AutoNumber field, and make the other two Text fields.
- 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
- Save the table, and then enter the following data in the table:
AutoNumber Field1 Field2
--------------------------
1 zzzz dddd
2 dddd yyyy
3 mmmm iiii
- 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
- 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
- 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
- Set the AutoNumber field's UniqueIndex property to Yes. The table is sorted as:
AutoNumber Field1 Field2
--------------------------
1 zzzz dddd
2 dddd yyyy
3 mmmm iiii
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate