Changing Data Type - Text to ReplicationID

  • Thread starter Thread starter BlakeD
  • Start date Start date
B

BlakeD

I have two tables (tblClients and tblEmployees). Field [Counselor] in
tblClient uses the PK of tblEmployee as a lookup. Currently, the PK is
a text field containing the person's name.

I want to add a ReplicationID field, and make that the PK. I then need
to change the Counselor field in tblClient to store the ReplicationID
but show the Name, and not lose any data in the process.

Can anyone provide some help on how to do this, please? Boss wants
this project done by week's end, and this is my biggest stumbling block.
 
Make a copy of tblClient, naming it TrialClient, or some such. This copied
table won't be in your relationships chart, so you should be able to
manipulate it as you please. You will add a field above the Counselor field,
and call it ReplicationID, or whatever you want. Then take the PK off of the
Counselor field, and put it on the ReplicationID field. Populate the
ReplicationID field at this time. Save your work.

Before doing anything else, do a backup of your database.

Once backup is done, delete the original tblClient. Rename TrialClient to
tblClient. If you have anything in your relationship table that pointed to
Counselor, go in and delete that link, and link it to the ReplicationID field.

You should have everything that was in the original table, but with
ReplicationID added.
 
Do you have a particular reason for wanting a ReplicationID field? If
not, an autonumber would be simpler.

Do a google groups search on "replicationid kaplan" (without the
quotes) before you decide on the former.

HTH,
TC [MVP Access]
 
I attempted what you listed above, but I end up with a blank field in
tblClient, I'd have to walk all 2K records by hand and select the
values. I'm trying to find a way to do this without doing that as I
have 3 situations similar to this and I'm hoping to adapt whatever
solution is reached for this to the others. Else I'm walking 6K
records.

And TC - Yes, I'm sure I want to be using a RepID/GUID. Thanks for the
link, though. I did read up on it.
 
Perhaps I am misunderstanding what you want to do. Looking back at your
original post, it seems that you want to add a new field, called
ReplicationID. You would then want to populate that field for all the
current records that exist. I think I missed that in my directions to you.
However, you then say that you want to have the Counselor field (which
currently contains a number corresponding to the PK in the tblEmployees) to
something that contains both the replicationID and the Name? I may be
misreading that part. It seems you would only need the new field of
replicationID and the Counselor field as it stands now. You could then
combine both in a query or a report, if you wanted to.
 
New Instructions:

Make a copy of tblClient, naming it TrialClient, or some such. This copied
table won't be in your relationships chart, so you should be able to
manipulate it as you please. You will add a field above the Counselor field,
and call it ReplicationID, or whatever you want. Then take the PK off of the
Counselor field, and change the Data Type to Number, if it isn't already
that way. Change the Data Type for the ReplicationID to AutoNumber, and
change the Field Size to Replication ID (which should automatically change
Indexed to Yes (No Duplicates)). Put the PK on the ReplicationID field.
Save your work.

Check to see that the ReplicationID field has populated with your new
Replication ID's.

Before doing anything else, do a backup of your database.

Once backup is done, delete the original tblClient. Rename TrialClient to
tblClient.

You should have everything that was in the original table, but with
ReplicationID added. I believe that anything that was pointing to Counselor
in your relationships table, should still be pointing to Counselor.
 
"Replication ID" is a "field size" choice - like "Long" - for the
Numeric field type.

HTH,
TC [MVP Access]
 
Back
Top