adding autonumber field after the fact

[Guest]

I've inherited a project with three tables. One has an ID field (number)
which is manually set, related to each of the other tables in a one-to-many
relationship (each of the other tables having an ID field).

The tables already have data in them...lots of it.

I want to give the base table an autonumber primary field and link it in a
one-to-many to the other tables, the way it should have been done in the
beginning. But I don't want to re-enter data.

How can I do it?

Jerry

 

[Steve Schapel]

Jerry,

Make sure you have a backup copy of your database.

First of all, simply add a new Autonumber field to the first table.

With each of the other two tables, proceed like this...
1. Add a new field with Number data type
2. Make a query with this table and the first table, joined on the
existing ID fields
3. Make this an Update Query (select Update from the Query menu)
4. Add the new number field from the "many-side" table to the query
design grid, and in the Update to row of the grid, enter the name of the
new autonumber field in the first table
5. Run the query (click the toolbar button with the red [!] icon
6. Delete the existing ID field
7. If required, rename the new fields to ID