renumbering secondary databse fields.....

# Thread: renumbering secondary databse fields.....

1. Devorah Guest

## renumbering secondary databse fields.....

Hi again!<BR>Here&#039;s something fun:<BR>I&#039;m doing a ranking of folks and I want to be able to renumber the ranking field in every record in the table when I add or delete a record, or when I edit that particular field.<BR>Does anyone have any clues on how to do this or at least how to approach it logically?<BR>Thanks,<BR>Dev<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Hard or easy???

*MAYBE* this is simpler than I first thought.<BR><BR>If you have people in the table with a "Ranking" field and the values in that field are (say) 1 to 37 [37 people in the table], then...<BR><BR>You want to delete the person who is ranked as number 19, say.<BR><BR>SO:<BR><BR>DELETE FROM table WHERE Ranking=19<BR>UPDATE table SET Ranking=Ranking-1 FOR Ranking &#062; 19<BR><BR>Makes sense? So the person with Ranking=20 now has a ranking of 19, etc.<BR><BR>To insert a person:<BR><BR>Well, I guess *YOU* have to figure out where that person is ranked. Let&#039;s say Joan is going to be inserted and given a ranking of 11. SO:<BR><BR>UPDATE table SET Ranking=Ranking+1 FOR Ranking &#062;= 11<BR>INSERT INTO table (Person, Ranking) VALUES(&#039;Joan&#039;,11)<BR><BR>Not sure why you want to do this, but looks simple enough!<BR><BR>Just be sure you don&#039;t have two people updating the DB at the same time! Especially when inserting a new person, since the calculation of her/his ranking might be *very* dependent on the data staying stable, you might want to do Application.Lock/Unlock around the entire process.<BR><BR><BR>

3. Devorah Guest

## Thanks, Bill! You're great!

=D

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•