Create master linkable table

[Matthew R White]

Good Morning,

I have a database with the master table having a field called customer number. Typically a 4 digit field. Each year the customer is with us, his file is duplicated and the new file increments with a second field called renewal number. Example the first year he is a 0 in the renewal field, the second year file he is a 1, etc.

This master table has 50,000+ records in it, with some customers having been with us for over 30 years. All other tables use these 2 fields to link in to the correct customer file.

I'm trying to create the ability for my employees to link different customer accounts. Like if they have multiple policies with us. They would be linked by the customer number field.

Are there any articles or forum posts that can help me understand how to do this?

[bcooney]

I believe your data model is working against you. imho, you should have had a related table to record a customer's history. A customer should not be duplicated.