Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unanswered: Autonumber existing field

    Hi all,
    I've got an index field with sequential numbers in it that I'd like to convert/copy to an autonumber field. So I create a new field set it to autonumber and hope they line up (that is CurrentID = NewID) of course they don't. That is Current ID (0, 1, 2, 3, 4) doesn't equal NewID (4, 1, 3, 0, 2). What confuses me is what order is creating the autonumber pattern (guessing) entry order and how to change that. Changing all the current IDs isn't really an option. Anyone have a solution to this conundrum?

    T

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    What about this....

    Start off with your new, blank table (with the autonumber column created in place of the CurrentID number field).

    Then do a INSERT INTO NewTable (a, b, c) SELECT a, b, c FROM OldTable ORDER BY CurrentID

    Important notes:

    The ORDER BY CurrentID in the SELECT Statement "should" force the rows to go in the same order as assigned currently. Be sure you don't try to insert the CurrentID into the table, just reference it in the sort.

    Also, autonumber columns cannot begin with 0 AFAIK, so if you have an existing record with CurrentID = 0, you'll have to handle that one manually.

    Lastly, be sure to verify there are no duplicate values or gaps in the sequence of your CurrentID field. If so, then this approach will not work.

    I haven't tried this myself, but it's the first thing I'd try.

    Good luck!


    Quote Originally Posted by tsweeney
    Hi all,
    I've got an index field with sequential numbers in it that I'd like to convert/copy to an autonumber field. So I create a new field set it to autonumber and hope they line up (that is CurrentID = NewID) of course they don't. That is Current ID (0, 1, 2, 3, 4) doesn't equal NewID (4, 1, 3, 0, 2). What confuses me is what order is creating the autonumber pattern (guessing) entry order and how to change that. Changing all the current IDs isn't really an option. Anyone have a solution to this conundrum?

    T
    Last edited by kaeldowdy; 08-24-06 at 18:04.
    Kael Dowdy, MCSD, MCDBA

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think the approach kaeldowdy has suggested is a good one. I would just tweak it a little. I would copy your current table with copy and paste. When you paste it, only copy the structure, not the data too.

    Then in the new table (the copy) change the CurrentID to Autonumber field. Then create an Append query that will append all of the records from you old table to the new table. It will only work if CurrentID is unique. And I am not sure what will happen with the 0. Then the next record you add should be Max(CurrentID)+1 in the CurrentID field.

  4. #4
    Join Date
    Nov 2004
    Posts
    10
    Thanks fellas that approach will work. I'll need to work with the original talble a bit there are a few missing ids. How I ended up goind it was creating the new table via copy/paste (structure only) then just copying and pasting the records, made possible by hiding the ID fields (in current table and autonumber table as well) so that they didn't conflict.

    Thanks

    T

Posting Permissions

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