Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Modify a Table Field to Be Auto-Number

    I have search through Microsoft stuff, but can't find any way to change the data type of a field to AutoNumber once data has been input. I have a field that would work much better as an AutoNumber. Any suggestions on options. Again, Thanks for your support. Judy
    Thanks much, Judy Crawford

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify a Table Field to Be Auto-Number

    In the database window, highlight your current table, right click, click Copy. Position in a blank portion of the database window, right click, paste only the structure (not the data). Now you have a table with the same structure as your current table. Modify this new table by adding an autonumber field. Then, write an append query to append all the records from your current table into the new table. The autonumber field will have values written to it during the append process. Rename and use this new table.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Modify a Table Field to Be Auto-Number

    Thanks for your reply and I can see how that would work in most circumstance. With this one I want my student number to become an auto number field, and I would like to maintain the numbers up to point XYZ. Your method gives an entirely new auto number field. I think I probably will just have to live with my current method. Thanks so much, Judy
    Thanks much, Judy Crawford

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Table Field to Be Auto-Number

    You can create a new table with the same structure by copying the structure only from the existing table. Change the field in question to an autonumber, which will work because there is no data in the table. Then create an append query and append each of the fields in the old table to the fields in the new table, including the field you changed to an autonumber. As long as the original field was numeric, your values will be appended to the autonumber field, and the next record you enter should be one above the highest value you appended.

    One warning, though. If you're using Access 2000 and do NOT have SR1 applied, the autonumber will try to start at 1 instead of the next correct number. That's one of the best reasons I found for applying the service release.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Modify a Table Field to Be Auto-Number

    Thanks Charlotte, I will try this trick with my database. Your help is appreciated. Judy
    Thanks much, Judy Crawford

Posting Permissions

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