Prev: Upgrade to Access 2007 problem
Next: hiding slides
From: SITCFanTN on 12 Apr 2010 13:08
I have a table that has an auto number primary key. I want to renumber my
records beginning at number 1. Because I've deleted some records from years
ago, the first record in my table begins with 4587. I currently have 1587
records in my table. Is there a way to do this quickly?
From: Daryl S on 12 Apr 2010 13:29
SITCFanTN -

You can do this quickly if this is the ONLY table that contains these
numbers. If you have any other related tables with this number in them, then
you will need a more complex method to keep the data in sync. In any case,
back up your data before you make changes like this!

The easy way to do this is to copy the existing table and paste it as a new
table with the structure only - no data. Then either write an append query
to append all the records from the old table into the new table, but do not
append the primary key - it will number those for you. If you want the
records in the same order as in the old table, then make sure your append
query sorts by the old primary key, but that it does not append that field
into the new table. (You can also copy/paste the records from the old
table, not selecting the primary key field, but before pasting in to the new
table, you should 'drag' the primary key field all the way to the right so
nothing will be pasted in that field).

Once you verify the data is what you want, you can rename the old and new
tables so the new table has the old name. Remember if there are
relationships using this primary key field, then you will need a lot of
additional work to get the related tables corrected...

--
Daryl S


"SITCFanTN" wrote:

> I have a table that has an auto number primary key. I want to renumber my
> records beginning at number 1. Because I've deleted some records from years
> ago, the first record in my table begins with 4587. I currently have 1587
> records in my table. Is there a way to do this quickly?
From: Jerry Whittle on 12 Apr 2010 14:14
An autonumber primary key isn't suppose to have any meaning. It's just the
glue to bind records together in other tables and point to a unique record. I
recommend leaving things as they are now.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"SITCFanTN" wrote:

> I have a table that has an auto number primary key. I want to renumber my
> records beginning at number 1. Because I've deleted some records from years
> ago, the first record in my table begins with 4587. I currently have 1587
> records in my table. Is there a way to do this quickly?
 | 
Pages: 1
Prev: Upgrade to Access 2007 problem
Next: hiding slides