Change the order of records in a database

[Horrid]

I am probalby just missing something but I can't change the order of records in a database. I need to be able to add a new record at any location and move records so that the user can arrange them in any order. The records are used to create a web page that displays the database info so the order can be very important.<br>
This is my first database app so I can't give much more info other than I used the Visual data manager.

 

[hug]

use the ORDER BY fiedyouwanttoorderdatabasewith to do it

 

[Horrid]

thanks hug,<br>
unfortunatly I have no SQL knowledge and I don't think that it achieve what I need. I don't what to order the records in any logical way. I need a way the user can move a record throught the database by clicking a move record forward/back button.

 

[Horrid]

I finally came up with a solution. Not a very attractive one but it does the job.<br>
<br>
copy all data from record 1 into variables, move to record 2 and copy all data into variables, write record 1 data into record 2, move back to record 1 and write record 2 data over record 1. <br>
someone please tell me there is a better way.

 

[hug]

WOW! you wanted to swap values, then that's the way to do it.

 

[chiph]

Hello Horrid!<br>
<br>
What we've done is add an sorted field to the record with the datatype being a float, not an integer. When you insert a new record after an existing record, you take the SequenceField value of the record you're inserting after, add .01 to it, and store that in the SequenceField of your inserted record. When the data is refreshed, it magically appears in order. Good for up to 99 insertions, but you could do it in increments of .001 for 999 insertions. At some point we run through the table resequencing these values to allow easier inserts later.<br>
<br>
This technique has the benefit of the records always being in order, even if you use a 3rd party reporting tool like Crystal. Downside is that you have to remember to put a &quot;ORDER BY SequenceField&quot; at the end of your SQL.<br>
<br>
Chip H.<br>

 

[RustyG]

I think you're getting a little mixed up with orders! In a relational database such as Access, SQL Server etc, the order the data is stored in has little or no relavance. It is the order the data is RETRIEVED that counts. Without an ORDER by clause (or a default index like the primary key) you will never be able to assertain in advance the order in which rows are returned.

 

[calahans]

I agree with RustyG, a programmer should never concern himself about the physical ordering of records on a database, except if you are designing a clustered index. I have never come accross a situation where I need to replace record's physical locations. Research the ORDER BY clause, you can get whatever ordering you require from using it well (perhaps in combination with chiph's extra column).<br>
<br>
C