There has been much confusion in regards to the order of records in an Access database. Why is that? Because records in a database are not saved sorted in any particular order. Just because they are entered 1-2-3 does not mean they will be returned 1-2-3 when requested through a query or even when viewed in a table datasheet.
A database stores data on pages. As additions, updates, and deletions occur, these pages can get fragmented and rearranged over time. The amount of data in a record will sometimes determine whether a record gets stored on one particular page or gets saved to another.
Take a table with the following entries:
Conceiveably, one could get 6 differently ordered result sets back from the following query:
SELECT * FROM MyTable;
Some things that can impact the order of records returned are:
If you remember nothing else about this article, remember this:
>>> The order of records that get returned in a result set can only be guaranteed if an ORDER BY clause is included in the SQL. <<<
So if you need your records to be returned in a particular order every time, just include an ORDER BY clause in the underlying SQL.
SELECT * FROM MyTable ORDER BY MyColor
SELECT * FROM MyTable ORDER BY MyDate, ID
SELECT * FROM MyTable ORDER BY MyNumber DESC, MyColor
Getting first or last records
Another common stumbling blocks for people new to Access is to use First() or Last() function. The name is somehow misleading as it does not actually return the first or last records of a given query but rather returns the first or last entered record for that given query. Thus, using the above table, if 2nd record was the first entered by the user, that is what First() will return. The correct way to obtain first or last records is to use Min() or Max() functions with the appropriate criteria in the WHERE clause.
|This page has been accessed 5,402 times. This page was last modified 01:41, 10 February 2012 by Jack Leach. Contributions by BananaRepublic and Walter Niesz Disclaimers|