appending record ID numbers from PK table to the related FK in another table

[SepieSmith]

Hey, sorry for the not-so-clear title! I'm pretty new to access although I understand things pretty fast.
I'm trying to amend a mess in my employer's dB (Insurance agency) Here's my problem:
Table 1 holds the PolicyNum which was created as the Primary key. This table is connected to Table 2 which holds additional insured persons in that policy.

I want to add an ID field for table 1 (to be used as PK instead of the PolicyNum) and relate it to a new foreign key field I will create in table 2 (currently the FK in Table 2 is the PolicyNum of course).

Problem: If I change the PK on table 1 Access won't automatically copy the PK values to the new FK field in table 2.

I tried every kind of query I know - but I wasn't able to do it. Here's the gist of what I gotta do:


Table1
ID(PK) PolicyNum
01 15123456789101113
02 15123456789101114


03 15123456789101115


Table2
Policy_ID(FK) PolicyNum
?? 15123456789101113
?? 15123456789101114
?? 15123456789101115


how can I get Access to return/copy the value in the ID field in Table1 - onto the Policy_ID field in Table2 at the appropriate row (with same PolicyNum)?

your help will be very VERY appreciated!!
Thanks in advance!

[Micron]

Usually I don't ask why, but in this case, I see absolutely no gain for the work, and suspect it will not solve whatever problem it is you think you have. Based on the limited information, the data in your second table should probably look like this (assuming 2 to 3 additional persons covered under the policy).

Table2
Policy_ID(F'PolicyNum	Addl_Ins
15123456789101113	Mary
15123456789101113	John
15123456789101114	Peter
15123456789101114	David
15123456789101114	George
15123456789101115	Frank
15123456789101115	Mary

If you're bent on doing this anyway, create the ID (as an autonumber?) and PolicyID fields. If not an auto id, then create the unique row ids, then run an update query against the second table, updating the PolicyID field to equal the ID field where policy# equals policy#. Again, I see no value in this based on your posted info. ALWAYS test suggestions on copies of your tables first.

[June7]

Perhaps the gain will be more efficient search/sort with the autonumber PK. This might make a difference in a very large db, but if this is fairly small, the gain will likely not be noticeable.

As micron suggests, run an UPDATE action by linking on the existing Policy_ID fields.

What is the relationship of these tables - 1 to many as described by micron?

Once all is good, the Policy_ID field in Table2 can be deleted.

[Micron]

Once all is good, the Policy_ID field in Table2 can be deleted.

A good point in support of normalization, but issues can arise if queries or any record/row sources will be looking for that field. They'd all have to be modified. More work but as for what end, I don't know yet.

[June7]

Yep, changing PK/FK is not a simple task in a fully developed and working db. Is the effort worth it? Only OP knows. Why do you feel the current design is a 'mess'?

[orange]

sepiesmith,

As the others have mentioned, I also would like to know more about your requirement. Is there a specific issue/problem that is driving this decision?
Backup! Backup! and test on a copy of the database if you do proceed. Document your steps.
Good luck.

[SepieSmith]

Guys thank you so much for all the replies!

To address your concern about the gain of the change - I'll just point out that this dB was NOT created by a professional (seems like CEO wanted to save a few bucks and go for Access instead of a Server-Client SQL dB...) and the fact is that the PolicyNum field is in fact a short text field and NOT a number field (!!!) - I have not a clue why, but that's how it is... I tried resetting the field type to a number (decimal) only to discover that the policy numbers were not entered in a consistent manner (sometimes there were "/"s and extra "0"s which was possible because the field length was set to 255 char...) that settings allowed for duplicate policy numbers to be entered (e.g same numbers and a slash in the middle...). Changing the PK and FK for the policies is the first step to trying to amend this dB. My next move would be to try and clean the data in the PolicyNum field... So stay tuned! :-)

You've been very helpful! I'm gonna try Micron's idea and update!
Thanks everyone!

[June7]

Alarms are blaring!!! Do you mean the numbers are almost duplicates, maybe varied only by the inclusion of an additional character? In which case the numbers are not really duplicates. What you need to do first is identify the dependent records associated with these 'duplicates' and edit them for whichever version of the 'duplicate' you want to use. Then run the UPDATE action for the new PK/FK if you still want to switch over.

[SepieSmith]

hmm.. never thought about doing it in this order...
Let me just make sure we mean the same thing -
I have a policy number that looks like this 123/45/67 with say 3 dependent records.
I also have a policy number that looks like this- 1234567 with or without the dependent records...
If I try to remove the /'s from the PolicyNum field I'll essentially create duplicates on the PK. So in order to remove them, don't I need first to create a different PK and FK (so that I'll be able to remove the /'s and than retrieve the duplicates from both tables with a select query?)

[June7]

If these two policy numbers are really for the same policy and you want to use the number without /'s, must first fix the dependent records so they will correctly associate with the proper parent record when updating for the new PK.

As an alternative method - remove the PK assignment from the policy number field, make sure Cascade Update is set in Relationships builder. Edit the PK value in parent table and this edit will 'cascade' to the dependent records. Now delete the undesirable 'duplicate' parent record. Run UPDATE for new PK.

[SepieSmith]

Yes! that actually makes sense to me! I'll try it and post my results.
Thank you!

[Micron]

I would ensure the "child" records are in fact, related to the 'duplicate' policy number by checking both sets to see if they're the same. If there are none, you should be OK. If you haven't been told yet, make sure you backup the db before making such changes. If you really understand the db design, you might be OK with just backing up db objects (tables/forms etc), but in order to not be surprised by things like cascade deletes/updates, you might want to back up the db before making major changes.

[June7]

None of this will actually prevent the distortion of policy numbers. Policy numbers are not really 'numbers' - they would not normally be used in arithmetic operations - they are identifiers. Identifiers really should be text but as text they are subject to the distortion you experienced. Input Masks can help prevent this. Considering how many digits in your policy numbers, think will have to be text field.

Even if not the PK, should probably set the policy number field as unique index so duplicates cannot be entered into the parent table.