renumbering secondary databse fields.....

Results 1 to 3 of 3

Thread: renumbering secondary databse fields.....

  1. #1
    Devorah Guest

    Default 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. #2
    Join Date
    Dec 1969

    Default 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. #3
    Devorah Guest

    Default Thanks, Bill! You're great!


Posting Permissions

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