Copying a set of records across several tables to the same tables with a few changes.

[IncidentalProgrammer]

Hi, all!



One of the main things I'm going to be tracking in my database is insurance policies; BIG insurance policies. There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.

I'm thinking I'm going to have to do this through a pretty large query. There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked? Could that work?

[pbaldy]

See if this helps:

http://allenbrowne.com/ser-57.html

[NoellaG]

Hi,

I wouldn't copy the entire records, but create a history tables containing only those fields that can change when renewals take place. In this case the original policy ID remains the same, but 1 policy can have many history records. This would make linking the data to the original policy much easier and avoids copying static data.

[IncidentalProgrammer]

Thanks, guys!

I've been working on combining those ideas, but I've hit a bit of a snag. I figure the data in the related tables, such as coverages, can remain the same, and I can just have items that have been cancelled filter to the bottom of the list in the subform. However, these tables have until now been linked by the policy ID (which changes at each renewal), and when I switch them to link by the sequence ID (same throughout the life of the policy), I end up getting indeterminate relationships. I know WHY they are: the sequence ID repeats itself in tblPolicy as the policy renews year after year, and repeats in the related tables, as it links each of the records to the policy (to use coverages as an example, there may be one or several records per policy, because each type of coverage is one record--in the form, the policy is already selected, and the user picks the type of coverage from a drop-down, and the limit and deductible and such).

So my question is: is it going to cause problems down the road if I leave the tables linked by the policy ID so that they can remain 1-to-Many?

[NoellaG]

Hi, what I've seen with insurance companies is that they have a table tblContracts hat has a 1 to many relationship with the table tblPolicies which keeps it's own ID that is linked in 1 to many relationships to the other tables. So you can keep the existing structure, just add 1 table on top of policies.