ACC1x: Record Sort Order Is Different Than Expected

• Microsoft Access versions 1.0, 1.1

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>
   

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.

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.

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

   SEAF
   APPZ

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.