Need to strip autonumber, resort data to new order, replace autonumber to make new index numbers
Need to strip autonumber, resort data to new order, replace autonumber to make new index numbers
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
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
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.
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.
Bob Fitzpatrick
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.