Creating an unique ordered number field with auto shifting values.

[TaSMaNiaC]

Hi,

I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously. Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries. Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3. I hope I've described this properly and it makes sense. I've been tinkering around trying to figure this out but I have no clue how to actually achieve this. Could anyone be as helpful to point me in the right direction?

Thanks in advance!

Tasman

 

[jdraw]

When you cross post/double post you should advise the reader that you have done so.
This will reduce unnecessary effort by those offering help.

Please post the cross reference link as well.

Cross posted at
http://www.accessforums.net/access/creating-unique-ordered-number-field-auto-shifting-35323.html

Here is why this courtesy should be done:
http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

Good luck with your project.

 

[ypma]

An interesting one, What triggers a move up the queue ? is it a date or a value and would it be unique ?

 

[rkl122]

Based on thinking about a similar situation of my own (not tested), here's a suggestion:

1. Put the current rank for the record to be "moved" into a variable, and the desired new rank in another variable. I'll call them CurRank and TargRank. There are various ways to do this.

2. Put the CurRank record "aside" by, say, temporarily assigning its rank = 0. This creates a "hole".

3. Select all records whose rank is > TargRank and < CurRank

4. Loop through the resulting recordset, incrementing each record's rank. The first "hole" is now filled and there's now a new one into which the original record can be placed.

5. Select for that record (rank =0) and set its rank to TargRank.

This outline should work for the case where TargRank < CurRank. A little additional elaboration should be able to handle the opposite situation - ie where TargRank > CurRank.

Since I don't particularly relish the notion of looping through a potentially large recordset, I'm hoping someone may suggest a better alternative that could help both of us.

-Ron

 

[ypma]

Ron
If the queue was based on a value or date ,1 would have a suggestion, still await reply to #3 other wise out side my comfort zone.