User-sort (1 Viewer)

[ListO]

I have a table which needs to be manually put into the order needed by the user. The order required can only be determined by the user.

I am seeking a way to gracefully do this. I've made it work by adding a sequence field for the user to set and then running a sort based on that field, but this is very clumsy.

Ideally, I would like be able to show the table in datasheet view and allow the user to simply grab a record and drag it to the desired position, much like the TAB ORDER window works in design view.

Any suggestions?

 

[S]

I think that the only way to do what you want is to export the table in excel where your users will be able to move and modify the records.
Good luck, S.

 

[Pat Hartman]

You can attempt to simulate drag and drop in code but I think it will be difficult. And I don't have time to think about the problem in sufficent detail to even offer advice on how to start. Behind the scenes in the code, you would be manipulating the sequence number. The solution you have chosen is probably the most reasonable and one which I have also used.

The underlying constraint and probably the reason this functionality is not built into Access is that relational tables by definition are unordered sets of data. We define unique keys so that we can identify a specific member of the set. And the only available way to return an ordered set of data is to specify an order by clause in the select query. When you run a query without an order by clause, the sequence of the rows returned is "undefined" even when the table has a unique primary key. In small recordsets where the data was entered in order by key sequence, you may think the query is returning an ordered set but that is just by chance. When you open a table in datasheet view, Access creates an SQL statement behind the scenes and includes an order by clause using the primary key. So, the tables always appear to maintain their sequence.

 

[Axis]

If you only need simple sorts, here's a solution I've used before. Show the table as a continuous form, with the fields names as buttons in the header. Clicking on the button resets the form record source to a query that sorts for that field, so the user can click on any button and show the table sorted any way they want.