Microsoft Access Tips for Serious Users

Provided by Allen Browne, from a posting by Julie Sheets. July 2008.


Bug: Records disappear when you sort them

Screenshot of Sorting button

When you click the Sorting buttons on the toolbar or ribbon, you may end up with fewer records than you had before you sorted them. This can happen anywhere the Sorting buttons are available — in a query, in a form, or in a report — and in any version of Access.

With certain queries, only unique records are shown after sorting. It behaves as if you set the query's Unique Records property to Yes, or added DISTINCTROW to the SQL statement.

To see a simple example (shown in the screenshot), download the demonstration database (Access 2000 format, 12kb zipped)

Demonstrating the bug

The sample database has a junction table (tblComputers) between two other tables (tblEmployees and tblManufacturers.) Query1 uses all three tables, but displays fields from the lookup tables only.

As the screenshot shows, the example query generates 6 rows. Now click in the LastName column, and click the [A-Z] sorting button on the ribbon. After sorting, the query has only 4 rows.

You also lose two records if you create a form or report based on this query, and click the [A-Z] or [Z-A] sorting buttons when viewing the form or report.

The records return if you clear the Order By property of the query, form, or report.

The query statement for this query is:

SELECT tblEmployees.LastName, tblManufacturers.MfgName
FROM tblManufacturers INNER JOIN (tblEmployees INNER JOIN tblComputers
  ON tblEmployees.EmpID = tblComputers.EmpID)
  ON tblManufacturers.MfgID = tblComputers.MfgID;

Factors

Indexes are not a factor. The bug occurs whether the field you sort on is indexed or not. Similarly, the bug occurs regardless of whether there are relationships (and so hidden indexes) between the tables.

The version of Access makes no difference. All versions we tested (from Access 2007 back to Access 97) display the bug.

Other common issues such as Name AutoCorrect make no difference.

You will not experience the bug if you output the primary key of the junction table. You probably do that instinctively in most queries, since working without a row identifier feels as bad as working with a table that has no primary key. Nevertheless, this does not excuse the flaw. Any database has a serious flaw if it gives you different results depending only on how you sort them. 


Home Index of tips Top