Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Question Unanswered: How to Fix up records with unique ID

    I have a database that for historical reasons has a table with a
    field, LineID, which currently always contains 0. This field ought to
    be the primary key of the table, but the value ought to come from
    another table that is an AutoNumber. I will fix the system to make
    both true, but for now I need to fix the data.

    My task therefore is to fill in the LineID records with a unique
    number so that I can mark it as a primary key. There is a max value of
    such numbers. The simplest option seemed to be something like UPDATE
    table SET LineID = ROW_NUMBER(), but access doesn't do that.

    The other fields of the table aren't sufficient to make a primary key,
    so I can't use solutions that require an "order by"; there's nothing
    to order on.

    At the moment the top runner seems to be move the table to SQL Server
    and run the SQL above there.

    Any other options?

    Thanks
    Ruth

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fix the RI link to the other table first
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Quote Originally Posted by healdem View Post
    fix the RI link to the other table first
    I'm sorry, I don't understand.

    LineIDs are literally copied from the other table. Currently there is no constraint, though there may be in the future.

    Thanks
    Ruth

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Copy the table, structure only, into a new table name.

    In the new table, change the LineID field type to AutoNumber.

    Us an append query to copy all of the data (except for the LineID field) from the original table into the new table.

    Once done, delete the old table and rename the new table with the name of the old table.

    If you no longer want the LineID field to be AutoNumber, change it to Long, or whatever.

    That's it.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Thanks, Ken!

    Ruth

Tags for this Thread

Posting Permissions

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