Access Database Sort Order

[davewhiting]

First off, I have tried searching but couldn't come up with an answer so I apologise if this has already been covered.

I have a database in Microsoft Access 2000. A while ago as part of a data cleansing exercise I used a Make Table query to make a copy of the main data table, but the records are now out of order. It's not a big problem, more of a frustration, as I can change the order in the table and the forms, but every now and then it reverts back to being out of order again, usually if I have made any changes to the userform that uses that table. If anyone could help me understand why it happens, and if there is a more permanent solution I would be most grateful.

Possible solutions I've attempted so far:
Set the Order by Property on the form and save the changes.
Set the sort order in the table and save the changes. These options seem to work temporarily.
Set the Order By property in the Make Table query. This appeared to make no difference whatsoever.

 

[jedraw]

The Order in which Access stores data is determined by Access. If you want data to be presented in a specific order, use a query with an Order By clause.
see

http://www.techonthenet.com/sql/order_by.php

 

[missinglinq]

To expand jedraw's response, the usual analogy used, vis-à-vis the order of data in Access Tables is that it is like a 'bag of marbles.' Rather than insuring that Records stay in the same order as they were originally entered, the Boys of Redmond gave us 'OrderBy,' which can not only be used with in a Query, as jedraw suggested, but can also be used in Forms and Reports, as needed.

Since something like OrderBy would be needed, even if the original order was maintained (after all, different orders are frequently needed, depending on the current task at hand) why bother to maintain that order in the first place?

If this kind of thing is truly vital to you, you need to include a means of sorting on the original order, such as a DateTime Stamp, a custom auto-incrementing Field, or by using an AutoNumber Field. The order of preference for these three methods, for most developers, would be the same as I gave them above, especially given the unreliability of AutoNumbers.



The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[davewhiting]

Thanks for the replies, I presume then that I can create a query that returns all the data from the table and set the order property there, and use that as the record source instead of the table.

 

[missinglinq]

Yes you can, or you can use the Form's OrderBy Property to do the same thing!

In Form Design View go to

Properties - Data - OrderBy

The Missinglinq

Richmond, Virginia

The Devil's in the Details!