custom sort order of table be able to rearrange records

[Robyn_P]

Hi again,

I'm just sounding this out at the moment, I think I can do it but am wondering if anyone has ideas on how to do it elegantly.

I've created a table to hold notes. I want be be able to organise these notes into any order I want and I want a form where I can select to move notes up or down in the list. The question is what's the best way to do this?

I'm thinking a table like the following

tblNotes


NoteID (primary key)
Note
SortOrder

If I start sort order off with numbers in increments of 1, then when I want to move a record up or down I add or subtract .5, add a query to sort my sort order and then renumber the column back to full increments, it would work but it seems pretty processor heavy.

Does that make any sense and has anyone done anything like this before?

I'll thrash out some early code now and post when I have something. In summary I want full flexibility to define the order that records are displayed in.

[Micron]

I guess the notes have no relationship to anything else, and it seems odd that the order is more or less randomly decided. By that I mean they're not related and not dated.
Sort order would be the only other option I could think of right now, but if it's not going to be a never ending thing for any particular note, then one option would be to number by something else, say 100, 200, 300... then you'd be able to just subtract some value from the sort value for about 98 times each.

Just realized I think I misinterpreted the actual goal. Forget specifics, but I still think the sort order field is your only option. I'll bet there's already code for this somewhere, but I'm trying to make dinner now!

[Micron]

I've seen something that seems to make this not too difficult if you have a secondary unique field. Probably could just be an autonumber field (ID).
Here's one example to get you thinking:
To move sort order 2 to the end of 5 records, update all by -1 where order >= 2 and <= 5 (say 2 has the ID of 22 for argument's sake).
Then update 22 to 5.
Hope these tables help:

What qry1 does to the sort order:

ID	SO	qry1
11	1	1
22	2	1
33	3	2
44	4	3
55	5	4

What qry2 does to the sort order:

ID	SO	qry2
11	1	1
22	1	5
33	2	2
44	3	3
55	4	4

The order of ID's and SO values when viewed sorted on SO:

ID
11	1
33	2
44	3
55	4
22	5

You could not make SO a unique index though. There are other ways, such as using the proper number type and playing with averages of the sort order values and subtracting or adding values, but they look complicated to me.