Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access Database Sort Order

Status
Not open for further replies.

davewhiting

Programmer
May 31, 2012
6
GB
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.
 
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!
 
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.
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top