Renumbering a duplicate ID field...

[Grim Reaper]

I am trying to fix a table that has duplicate ID numbers (they were
auto-numbered, but the person DID NOT make the ID number a primary key, so
there are duplicates).

What I would like to do is increase the value of one of the duplicate values
so that there will only be unique values in the database from now on (will
set the ID number to auto-number and primary key).

Anyways, the problem I am having is that I used the "Find Duplicates Query
Wizard" to find the ID numbers that are duplicated. Next, I wrote in
DISTINCT so that I will only get the first value that it comes to.

Finally, I wanted to update the value by just adding [ID]+1265 (end of the
database). But, I keep getting an error statement that says "Operation must
use an updateable query"

I have checked the help files, I have even took the auto-numbering off of
the ID field, still does not make a difference. (This database is updated on
a weekly basis with data).

Also, as a side note, the table only has 4 fields in it. If I try to include
one of the other fields in the DISTINCT query, it will not work correctly. I
tried to create a MakeTable query, update it, then join it with the original
table. That did not work cause it is only using one of the ID numbers so it
brings up ALL of the duplicate numbers.

Thanks for any help,
Grim Reaper

 

[Scott Simonson]

Try creating another table with the structure you want. Then Insert into it
the fields you want. That'll be the quickest way. Then rename the new table
to the present name.

HTH...

 

[Gary Walter]

Hi Grim,

If the ID field is not used as a foreign key
by another table, then why not just create
another field (say "ID2"), make it autonumber
and primary key, delete "ID", and change "ID2"
to "ID"?

Maybe it is more complicated than that?

Good luck,

Gary Walter