Help linking difficult data (2 system's extracts)

[GODZILLA]

Hello all,

I’m not too sure anyone will be able to help but I wanted to see if you had any ideas what I can do. Ok, here goes.

I have 2 tables from 2 different systems.

Table 1: All the policies that are due to renew. (System_A)
Table 2: All the renewed policies. (System_B)

The main problem I have is a lack of unique data. What I need to do is join table 1 to table 2 and bring back the new policy information. There are client numbers in both tables however they are not the same. There are policy numbers but again they are not the same.

What I am currently working with is the surname of the client and the first initial. Then looking at the renewal date (table 1) and the policy start date (Table 2) between -30 and 30 days. This is nowhere near perfect.

I need to find the volume of policies renewed/lost. Lost being that which is not bought back.

I will attempt to post an example database this evening with dummy data and see if anyone has any ideas. I am at a loss.

I have tried every method I can think of. I cannot pull more information unfortunately due to legal restrictions. Ideally (if I had built the system) I would have a unique ID that transcends systems, this is not available. I am trying to get the DOB and POSTCODE for all the information to try to build a unique string to base the join on. This is also a 1 to many and reverse issue. There could be one policy in T1 and 5 in T2.

If you have any ideas or comforting words, it would be appreciated. I am pulling my hair with this one.

As always thanks guys.

 

[Galaxiom]

You could add a field directly to the table on the "one" side of the system for client and policy but I would consider using join tables. These tables hold the tranalsation to connect records from the fields in each system.

Create a form that makes suggestions for potential join records based on the information that does match. The user would approve the suggestions by clicking a button on each record.

The lost policies would be the Null records resulting from an outer join.

 

[GODZILLA]

You could add a field directly to the table on the "one" side of the system for client and policy but I would consider using join tables. These tables hold the tranalsation to connect records from the fields in each system.

Create a form that makes suggestions for potential join records based on the information that does match. The user would approve the suggestions by clicking a button on each record.

The lost policies would be the Null records resulting from an outer join.

To give you a bit more information i am currently left joining the initial and the surname to bring back protential matches then filtering that by my between values. However the amount of data bought back is minimal. I have a similar method for working with data from a different system.

What im looking for is posisbly a method of joining that data that is more efficent than i am currently using.

Also i am using Access 2007.