Microsoft KB Archive/105978

{|
 * width="100%"|

ACC1x: Record Sort Order Is Different Than Expected

 * }

Q105978

-

The information in this article applies to:


 * 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:

 Make a copy of the Categories table and save the copy as Categories2. Open the Categories2 table in Datasheet view.  Add the following record:

     Category ID:  APPZ Category Name: Appetizers Description: Picture: 

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:

  Create a new query based on the Categories2 table as follows:

     Field name: Category ID      Sort: Show: True Criteria:  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:

  Create a new query based on the Categories2 table as follows:

<pre class="FIXEDTEXT">     Field name: Category ID      Sort: Show: True Criteria: a* or s* </li> From the Query menu, choose Run.</li></ol>

The following records will be displayed:

<pre class="FIXEDTEXT">  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:

  Create the following new form:

<pre class="FIXEDTEXT">     Form: Test1 ControlSource: Unbound </li>  Add the following combo box to the Test1 form:

<pre class="FIXEDTEXT">     Combo box: Field1 ControlSource: Unbound RowSourceType: Table/Query RowSource: Categories2 ColumnCount: 2 ColumnWidth: 1 in; 1 in </li> View the form in Form view.</li> View the items listed in the combo box.</li></ol>

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

Issue type : kbprb

Technology :