How do I create a numerically sequential field? 2

[whoschad]

Specifically what I'm looking for is this:

I need for a field in a table that will order each row sequentially at the user's whim each time a new record is input. For example, there is a hundred rows in the table. The field I need would have these rows ordered from one to one hundred, with no duplicates.
Then, whenever a new record is entered, the user should be able to input any number and have the rest of the fields that come after it change to match.

For example, I have one hundred records, all numbered sequentially. When a new record is input, the user types "50" in that field. What I would like is for Access to then take the previous entry of "50" and change it to "51" and then the entry with "51" to be changed to "52" and so on until the last record in the table would now contain "101"

At the end of the day, I wouldn't really need them to be exactly sequential, as long as I could order the rows by this field. I could have large gaps between the numbers, but this is undesirable because new records will be added all the time and it will start getting messy when I need to add a hundred new records that all fall between "5" and "6".


Thanks for your help.

 

[jebry]

Hi!

It would seem that the users are employing some logic to decide how to sort the records. If that is the case then just dump the random number field and sort the records based on the logic used. What you have described seems inefficient at best and, at worst, will leave you with incorrectly sorted records. All it would take is one user imputting an incorrect number and the sequence is thrown off and it won't be retrievable without considerable work.

Go to the people who requested this and talk them out of it!



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[traingamer]

Assuming that you are adding the records from a form, on the 'before update' event run a query similar to:

UPDATE yourtable SET yourtable.SortID = [SortID] + 1
WHERE ((([SortID])>newSortIDvalue));

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

That being said, I have to agree with Jeff in that a single mis-keyed [sortid] will make this a really ugly thing. In addition, if you are expecting to have more than one user or a large number of records, this will become even uglier and slow.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill