1 month + and still no luck, ranking records with the ability to change rank

[icemonster]

so i have this thing with a record, that when someone adds a record, a field counts the number of records or ranks the record from 1 to n then if the user wishes to change the rank of a record, he/she only changes the number of that field then the query would rearrange on its own, any idea how to do this or what's the best way to do this?

 

[Steve R.]

Use "ORDER BY". Fundamental Microsoft Jet SQL for Access 2000

 

[Beetle]

So you want to;

1) Have the record ranked automatically when it is initially entered?

2) Let the users have the ability to change the assigned rank number?

3) If the user does change the rank, you want the rank value for any existing records that are equal to or higher than the number the user entered to be increased by one and the recordset re-ordered?

How large of a recordset are we talking about here? This can be done but on a large recordset it may be inefficient and slow.