ACC1x: Record Sort Order Is Different Than Expected

ID: Q105978

The information in this article applies to:

SYMPTOMS

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

When you view data in a datasheet, combo box, or list box, the record order is different than you expect. For example, text fields may appear out of alphabetical order or numeric fields may appear out of sequence.


CAUSE

Microsoft Access has two default sort orders, insertion order and primary key order. These sort orders are used at different times, causing records to appear to be sorted differently than you expect.


RESOLUTION

If you want to see the insertion order for a table with a primary key field, remove the primary key from the table. For more information on removing a primary key, search for "primary key" then "Deleting the Primary Key" using the Microsoft Access Help menu.

If you want to store records in a different order than insertion order, build a new table with a custom sort. To do this, use a make-table query with a specified sort order. For more information on make-table queries, see the Microsoft Access "User's Guide," Chapter 7.


STATUS

This behavior is by design.


MORE INFORMATION

Insertion order is the order in which records are entered and stored in a table. This order may not be in alphabetical or numeric sequence, since records are often entered in a random order.

Primary key order is based on the field or fields in a primary key. This order has no effect on the way records are stored in a table; the records are still stored in insertion order. Primary key order changes only the way the records are displayed.

If the underlying table for a table, form, subform, or query datasheet has no primary key, the data will be displayed in insertion order. If the table has a primary key, the data will be displayed in primary key order.

There are two exceptions to this rule. For query datasheets, insertion order will be used for a query that has specific criteria but no specific sort order, even if the underlying table has a primary key. For combo boxes and list boxes, insertion order is used even if the underlying table has a primary key.

Steps to Reproduce Behavior

The following four examples demonstrate the two default record orders and the two exceptions described above. Note that all the examples use the sample database NWIND.MDB.

Viewing Records in Insertion Order:
  1. Make a copy of the Categories table and save the copy as Categories2.


  2. Open the Categories2 table in Datasheet view.


  3. Add the following record: 
    
      Category ID:  APPZ
      Category Name:  Appetizers
      Description:  <leave blank>
      Picture:  <leave blank>


Note the record order in the Category ID field. BEVR is the first record and APPZ is the last. This is the order in which the records were added to the table.

Viewing Records in Primary Key Order:
  1. Create a new query based on the Categories2 table as follows: 
    
      Field name: Category ID
      Sort: <leave blank>
      Show: True
      Criteria: <leave blank>


  2. From the Query menu, choose Run.


Note that the query datasheet is sorted alphabetically by Category ID, which is the primary key field.

Viewing Records in Insertion Order Despite a Primary Key:
  1. Create a new query based on the Categories2 table as follows: 
    
      Field name: Category ID
      Sort: <leave blank>
      Show: True
      Criteria: a* or s*


  2. From the Query menu, choose Run.


The following records will be displayed: 

   Category ID
   -----------

   SEAF
   APPZ

Note that SEAF appears before APPZ even though Category ID is a primary key field. Microsoft Access is using insertion order because the query includes specific criteria but no specific sort order.

Viewing Combo Box Items in Insertion Order:
  1. Create the following new form: 
    
      Form: Test1
         ControlSource: Unbound


  2. Add the following combo box to the Test1 form: 
    
      Combo box: Field1
         ControlSource: Unbound
         RowSourceType: Table/Query
         RowSource: Categories2
         ColumnCount: 2
         ColumnWidth: 1 in; 1 in


  3. View the form in Form view.


  4. View the items listed in the combo box.


Note that the items are displayed in the combo box in insertion order, even though the underlying table has a primary key field. 


Keywords          : kbusage TblPriky 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 27, 1999