UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Record Order    
Record Order

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:

ID (AutoNumber) MyColor (Text) MyDate (DateTime) MyNumber (Number - Long Integer)
1 Blue February 2, 2010 390
2 Yellow January 1, 2010 275
3 Red March 3, 2010 90

Conceiveably, one could get 6 differently ordered result sets back from the following query:

CODE
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.

Examples:

CODE
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.

Edit Discussion
Thank you for your support!
This page has been accessed 4,260 times.  This page was last modified 01:41, 10 February 2012 by Jack Leach. Contributions by BananaRepublic and Walter Niesz  Disclaimers