Creating new primary key with relationships

[Kyle]

I have been brought into an organization to rework an existing database
that tracks freezers and other controlled environment units at a
university. One of the primary issues has been to change the identifier
for each record from a location-dependent string to a unique numeric
identifier for each freezer.

I need to create a new field in the primary table (rather than alter
the existing ID field), link it to new fields in the secondary tables
that have relationships dependent on that unique UnitID (without
deleting or altering the existing identifier field), automatically
generate new ID numbers in the primary table and have them
automatically populate out to the corresponding fields in the secondary
tables.

Is this possible?

I tried doing it by creating a new field in the primary table called
UnitID set to Auto Number, then created like-named fields in other
tables and establishing a one-to-one or one-to-many relationship
(depending on the conditions), but the UnitID numbers, of course,
didn't show up in the new fields in the secondary tables. I tried
redoing the creation but not setting the UnitID to AutoNumber, created
the relationships and then tried to change the UnitID field in the
primary table to Auto Number, but again, of course, you can't change
the field without breaking the relationship.

With over 400 records in the database so far, and 500 more to come
shortly, I'd prefer not to have to do this manually. I'm not adverse to
using VB to write a script, but have MINIMAL experience with VB. Is
there a relatively simple solution out there?

 

[Guest]

Lots of work. First backup the database before you do anything!!

Do an update on all the tables to update the new field with the number in
the autonumber field.
Delete any relationships using the old primary key and rebuild using the
autonumber.
Revise any queries that were joined on the old primary key to be joined on
the autonumber.

 

[Kyle]

Karl, thanks for the instructions. Like you said, lots of work. I'll
post again, with details, when I've had success.

 

[Kyle]

It took me six minutes this side of an eternity to figure out how to do
what needed to be done, but now that it's done, it seems incredibly
simple.

As promised, and for the benefit of anyone needing to do the same and
finding this discussion through Google, here is step-by-step how I
copied the unique UnitID numbers from the primary/parent table over to
the secondary/child table. I would also recommend using Access'
built-in Help feature to find Microsoft Office On-line Help's 16 page
instructions titled "Changing existing data by using an update query".

BEFORE ATTEMPTING THIS - follow Karl's recommendation (above) and BACK
UP YOUR DATABASE. You will be making permanent changes to one or more
table fields, and if you have a lot of records, manually going back and
correcting an incorrect update query will be time-consuming.

(1) Create a new field in the parent table. Set Data Type to Auto
Number. On the General tab, set Indexed to "Yes (No Duplicates)".
(2) Create a new field in the child table (more than one table in my
case). Set Data Type to Long Integer. On the General tab, set Indexed
to "Yes (No Duplicates). NOTE: as Auto Number in the parent table can
only be set to Long Integer, and the Data Types in both fields must be
identical in order to create a relationship, make sure the Data Type
settings in the child table field are identical to the Data Type
settings in the parent table field.
(3) Close both tables. Open the Relationships window. If the tables
aren't already displayed, bring them up in the Relationships window.
Create a One-to-One relationship from the field in the parent table to
the field in the child table. Close Relationships.
(4) Create a new simple Query. In the Query, include all data for
comparison. In my case, I wanted to see FreezerID (the old ID number)
and UnitID (the new ID number) for every record in both tables, so I
had four entries in my Query design. Run the Query, and make sure that
the data being pulled is what you would expect.
(5) Once you get the data you expect, go to the Query menu, and choose
"Update Query". The lower half of the design window will change on you.
Since my UnitID field in the child table was empty and needed to be
populated with the data from the UnitID field of the parent table, what
I did was go to the column for the UnitID field in the child table, and
under Conditions right-clicked to build an expression. In the
Expression Builder window, I double clicked on Tables, clicked on the
parent table, and then double-clicked on the UnitID field in the middle
column, and then clicked OK. Close the query.
(6) Double-click on the query to run it. Access will give you two
warnings. The first warns you that you are about to update the table
data; if you're comfortable with that, click [OK]. The second tells you
just how many records you are going to update. If the number of records
matches what you expect, and you feel comfortable, click [OK].
Otherwise, you still have the option to click [No] and dump out of the
query. Look at the results, and see if it is what you expected.

It worked for me, and I hope it works for everyone else!

 

[Kyle]

UPDATE and CORRECTION of Step 4

(4) Create a new query using the "Find Unmated Data Query Wizard".
Select the table from which you want to get the data for the new field
in the child table, then select the child table. Select the two old
primary keys as the data contained in both tables that you want to
match up. Choose the old primary key and new (not yet) primary key
fields to be displayed in the query. Before clicking [Finish], chose
"Modify query design". When the query opens in Design view, add fields
to the query until you have the old primary key for both tables and the
new (not yet) primary key for both tables. Under one of your fields
you'll see in Criteria "IsNull" - delete that, save the query, and
close.
(4a) Run the query (double-click in the Queries window) and make sure
that the data for the old primary key in both tables matches up. If it
does, then close the query and go to Step 5.