Comparing two tables to see if there is any match

[jdraw]

I have looked at the data in Notepad??
And see no common PolicyNumbers in the 2 tables AnnutiesTBL
Or TBLCancellationModes.

 

[aman]

No but there might be..Ok see attached an example:

 

[aman]

Can anyone please help me in this? Its very urgent to resolve. ANy help would be much appreciated.

 

[jdraw]

aman,

Please provide some sample typical data. The data you have provided does not illustrate the issue --in my view.

 

[aman]

Jdraw, the lengths of the strings vary in both the tables. I can't change that as we got these two tables from the clients. The only thing is I need to match each policynumber of Tblcancellationmodes with all the policynumbers of AnnutiesTBL. As length varies so we need to use LIKE operator to check if each policynumber of Tblcancellationmodes is present in any part of the policynumber of ANnutiesTBL.

Unfortunately, I can't upload the access database so I have sent you the notepad file. Please check it again. As thus is how we need to match policy numbers of both tables..

Then I need to store ID,policynumber from Tblcancellationmodes and ID from AnnutiesTBL in a new table.

I hope it made everything clear this time..thanks

 

[jdraw]

I copied your sample data, moved it to 2 text files. Adjusted PolicyNumber to APolicyNumber and CPolicyNumber in respective tables. Imported the data into Access using the APolicyNumber and CPolicyNumber as text.
Tables were created successfully.

Created SQL

SELECT Annuities.ID
	,Annuities.APolicyNumber
	,CancellationModes.ID
	,InStr([Annuities].[APolicyNumber], [CancellationModes].[CPolicyNumber])
	,CancellationModes.CPolicyNumber
FROM Annuities
	,CancellationModes
WHERE (((InStr([Annuities].[APolicyNumber], [CancellationModes].[CPolicyNumber])) > 0));

And got the result in attached jpg.

NOTE: Because the PolicyNumbers are of varying lengths, I used the Instr function to find string within string.

I hope this is helpful.

 

[aman]

Thanks for this Jdraw. Sorry to be a pain but now there is little change in the requirement. Now I need to match each policynumber of TBLCancellationModes with policynumbers in Annuties but just in the beginning of the string. As now we use Instr function to see if the policynumber is present in any part of AnnutiesTBL policynumber. But now we need to check if Policynumner is present in the beginning of the Annuties POlicynumber string...

that means something like: TBLCancellationModes.PolicyNumber Like AnnutiesTBL.PolicyNumber *

and not in any part of the substring. Like in the attached notepad only two policy numbers match i.e 7003531, 7003541

Please see attached.

 

[vbaInet]

INNER JOIN AnnutiesTBL
On TBLCancellationModes.PolicyNumber LIKE AnnutiesTBL.PolicyNumber & "*"

 

[aman]

VBAnet, Its actually the other way around. The AnnutiesTBL contains extra digits after each Policy number. The TBLCancellationModes contains right Policy Numbers. So basically we need to match each policy number of TBLCancellationModes with all the policy numbers of AnnutiesTBL to see if there is any match.. if the match occurs then store TBLCancellationModes.ID,TBLCancellationModes.PolicyNumber,AnnutiesTBLID into a Table named "KLMAnnutiesTBL".

Thanks

 

[vbaInet]

Well if it's the other way around, swap it around in the SQL. I was going by what you stated in your previous post.

 

[aman]

VBAnet, If I write the below query then keeps on running .It is talking ages and nothing is coming up.

SELECT TBLCancellationModes.*, AnnutiesTBL.ID
FROM TBLCancellationModes INNER JOIN AnnutiesTBL ON AnnutiesTBL.PolicyNumber like TBLCancellationModes.PolicyNumber & "*";

 

[vbaInet]

How big are both tables? And are those fields indexed?

 

[jdraw]

...now there is little change in the requirement. Now I need to match each policynumber of TBLCancellationModes
with policynumbers in Annuties but just in the beginning of the string.

If the string has to be at the beginning, then you could still use InStr but change the position criteria to =1

SELECT Annuities.ID
	,Annuities.APolicyNumber
	,CancellationModes.ID
	,InStr([Annuities].[APolicyNumber], [CancellationModes].[CPolicyNumber])
	,CancellationModes.CPolicyNumber
FROM Annuities
	,CancellationModes
WHERE (((InStr([Annuities].[APolicyNumber], [CancellationModes].[CPolicyNumber]))[COLOR="Red"] = 1[/COLOR]))

 

[aman]

How big are both tables? And are those fields indexed?

VbANet, TBLCancellationModes contain 344,955 records and AnnutiesTBL stores 109,213 records. Yes the policy numbers in both tables are indexed.

 

[aman]

JDraw, The query is taking long to run... Its still running. Normally how long does it take ??Any ideas??

 

[vbaInet]

It's taking long because one policy number could match thousands of records in the other table, so think about how many times it has to do this.

 

[jdraw]

@aman,
With respect, you have tables that have a basic design issue in my view. The PolicyNumbers, if they are meant for identification and comparability, should have the same datatype and format. Since your PolicyNumbers can be of different lengths and seem to contain extraneous trailing 0s, they can not be tested for equality. You are trying to match patterns/substrings.

I can not tell you how long it will take to run the query against all of your real data. I can tell you that if this is a recurring process, then you should take steps to redesign to take advantage of the build in capabilities of the database system itself --consistent data formats.

Another observation is that your requirements seem to be changing and that is not a good sign for a production operation. Can you tell readers more about this process you are trying to automate/resolve? Is it recurring? Policies and Cancellations would tend to suggest insurance, people and finance. The current query attempts seem a little "amateurish" to be dealing with this.

However, if this is all a learning exercise, then you are experiencing some of the issues associated with database table and field design. I don't think that the LIKE operator can take advantage of indexing, so use of LIKE is not likely to lessen your query execution time.

As always, a clear description of your environment, your business and the current issue in business terms would be very helpful. Too often questions are posed in Access jargon with little background and without an analysis of options. I'm not suggesting you have done anything wrong, I'm trying to highlight that readers (and me specifically) are trying to respond with some focus to a problem we don't really understand.

I hope this is useful, and do hope you resolve the issues.

 

[vbaInet]

However, if this is all a learning exercise, then you are experiencing some of the issues associated with database table and field design. I don't think that the LIKE operator can take advantage of indexing, so use of LIKE is not likely to lessen your query execution time.

Good point jd. I think it depends on usage. If none of the wildchard characters are used then it may use indexing (which defeats the whole point of doing a pattern match). But the point of using LIKE in this case was to take advantage of the INNER JOIN which, naturally, performs better than a WHERE clause.

We can re-write it to look like this:

SELECT TBLCancellationModes.*, AnnutiesTBL.ID
FROM TBLCancellationModes INNER JOIN AnnutiesTBL ON TBLCancellationModes.PolicyNumber = Left(AnnutiesTBL.PolicyNumber, Len(TBLCancellationModes.PolicyNumber));

Then again indexing won't be used, however, it may perform better than LIKE.

Regardless of all that, jdraw has highlighted your main problem, so work with him on that.

 

[aman]

Jdraw I understand what you mean. Just to make sure its not recurring process. I just got these two tables and have to match them. I haven't created the tables. Yes I work in Finance/Insurance company. This is not going to happen again. I have given these two table to match the policy numbers. I just have to do it once and let them know as its very difficult to match the policy numbers for them(Layman).

Thanks

 

[jdraw]

Did the query finish execution? How log did it take? What PC/machine are you working with?