Results 1 to 6 of 6
  1. #1
    wizardnick is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    2

    Autonumber changes

    Need to strip autonumber, resort data to new order, replace autonumber to make new index numbers

  2. #2
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,167
    Quote Originally Posted by wizardnick View Post
    Need to strip autonumber, resort data to new order, replace autonumber to make new index numbers
    Why? Autonumber should only be used as a unique identifier. If you create a query based on the table it can be ordered in any way you wish.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    wizardnick is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    2
    Quote Originally Posted by Bob Fitz View Post
    Why? Autonumber should only be used as a unique identifier. If you create a query based on the table it can be ordered in any way you wish.
    Primary key using autonumber was used to generate Client number - had a need to delete old data (very large quantity(700+)) and was looking to resort and regenerate new client numbers

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Going off of memory.

    Backup your DB. Make some copies of your Table

    Then, in your original DB....

    Do a save as of your table. Build a query of your save as table and sort in the order you want. This will be your APPEND query.

    You can then import the original table from you DB backup, without the data.

    Do a compact and repair to reset the autonumber to zero.

    Run your Append query to append to the table you imported from the backup. There is a way to start your auto number from a number of your choosing but I don't recall how right this minute.

  5. #5
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,167
    Quote Originally Posted by wizardnick View Post
    Primary key using autonumber was used to generate Client number - had a need to delete old data (very large quantity(700+)) and was looking to resort and regenerate new client numbers
    But it doesn't matter that you have deleted 700+ records. The autonumbers are still unique. If you do as ItsMe suggested, you will change the value of the autonumber for each record. Any tables related to this table will then have the wrong value stored in their FK, which will destroy your data integrity.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I was going on the assumption that the OP knew for sure they would have to reorganize the relationships. Assuming the OP has this level of understanding they should be familiar with an append query. So ummm yeah, what was the OP's plan after the PK is deleted?

    If a record is "Old" then archive it with a Boolean. You can move your archived records to an archive table on a schedule (annually or whatever) if you fear you will have too many records.

    My bad. On occasion, I have had the need to restructure table design and reorganize relationships in DB's and used the described append technique by merging tables, appending with new key, then separating into new structure. Fun stuff.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. duplicate autonumber
    By rayc in forum Access
    Replies: 5
    Last Post: 06-19-2013, 07:50 AM
  2. Replies: 2
    Last Post: 01-15-2013, 10:04 PM
  3. Help with autonumber
    By GDubbs780 in forum Programming
    Replies: 15
    Last Post: 02-27-2011, 02:27 PM
  4. Autonumber gone crazy
    By asearle in forum Access
    Replies: 1
    Last Post: 07-27-2010, 05:41 AM
  5. AUTONUMBER
    By J A F F A in forum Database Design
    Replies: 1
    Last Post: 10-03-2007, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums