Comparing two tables to see if there is any match

[aman]

The client has given us two excelworkbooks. and wanted us to match the policy numbers against them. So I exported the data in Access database (Two separate tables: one for TBLCancellationModes and another one for AnnutiesTBL) . Is there anything like VBA that I can use to find out the matching policy numbers ...

 

[aman]

Jdraw, the query you sent me in post #33 was running for 1.5 hours without any suggess. I came to my desk after lunch break and check its still running and not displaying anything...

The query that VBAnet sent me in post #38 is running for 10 minutes and im still waiting for the results..

I will let you know what is the outcome from this one when the query will stop running.

 

[aman]

JDraw, The query you sent me in post #33 actually works. As I have created two sample tables and put 20 records in each and ran the query and it worked fine..

The only problem might be too many records in both the tables..

 

[vbaInet]

There might be a way. But for starters, get DISTINCT values of both policy numbers in both tables.

But still keen to know how long the queries you're running took.

 

[jdraw]

The client has given us two excelworkbooks. and wanted us to match the policy numbers against them. So I exported the data in Access database (Two separate tables: one for TBLCancellationModes and another one for AnnutiesTBL) . Is there anything like VBA that I can use to find out the matching policy numbers ...

The problem, as you have stated it, is much like comparing apples and oranges.
You are using the PolicyNumber as if they were the same thing in the two tables. However, they are represented differently -- format/length.

Getting back to my apple and oranges analogy:

The only thing in common is that both are fruit.
You could say, they are both red, except for the orange. But that isn't helpful.

Comparing phone numbers with house numbers is a similar issue. Phone numbers, at least for us in North America, have a defined pattern. House numbers have no such universal pattern. You could compare the house number (24) in the address 24 Princess Street with all phone numbers. And seek those with the digits 24 contained within a phone number --but what does that really give you/tell you? You could even limit the 24 to the start of a phone number???

In my view, and I still don't think we have all the info, I would be attempting to find a common numbering scheme between/from the Annuities and Cancellation files. I'd investigate the background of these files.

I have worked to match company names, addresses, phones, contacts etc from a variety of files in different formats (and languages). Matching a pattern may be helpful, but usually requires additional properties to make a decision.

 

[aman]

VBAnet, I am not supposed to remove duplicate policy numbers and my manager wants to see how many duplicate policy numbers for each.

And I stopped the query forcibly and there is no point waiting for hours without any result. Now I have split the TBLCancellationModes into few tables. Each table contains 80,000 records. and running the query again to see how long will it take.. its driving me mad..but will let u guys know the outcome.. thanks

 

[jdraw]

aman,

You have to have a list of things to do; then set priorities based on criticality/importance.
You may have to run a number of queries, but you have to understand and communicate the scope of the issue before attempting to solve it in bits and pieces.

To readers it appears that you do not have your problem defined. It seems the requirements are always changing. And for readers it is hard to hit a moving target.

 

[vbaInet]

There's a reason why I requested that you should get distinct policy numbers for testing purposes.

 

[aman]

Jdraw, you are absolutely right. Its comparing Apples and Oranges. But I have to do that as It is the job assigned to me and I have to do it once. We are not dealing with it in the future..believe me.. Just want to see how many policy numbers matched ..that's it really.. Thanks so much for your help so far.. will get back to u soon.

 

[aman]

Jdraw, the requirements are not changing..This is what I told earlier wee need to match the policy numbers..Again I had a word with account manager and he said in AnnutiesTBL the policynumber field is 14 digit long so we need to compare each policynumber from TBLCancellationModes with first 7 digit policynumbers of AnnutiesTBL

The main thing is the code that you gave me Works perfectly fine.. The only thing is too many records in both the tables which is taking ages to run..

 

[Dick7Access]

Aman what have you done? Doing the match against both tables is fairly easy, do some research.

Love it good advice. I always try and Google it before I come to AWF as I get more out of it than if somebody just gave me the answer!

 

[aman]

Now I have 80,000 records rather than 344,955 and lets see how long will it take to match against against all policy numbers in AnnutiesTBL.

 

[aman]

Hi Guys, My manager wants me to use count function that will run for each record in TBLCancellationModes to see if that policynumber exists in AnnutiesTBL or not. and if its present then store the details about the Policynumber in table named "KLMAnnuties".

He said count is much faster function than find/instr etc.. Any suggestions are welcome.. thanks guys for helping so far.. hope we will be able to figure out the problem soon.

 

[jdraw]

Count what? You have to do the compare to see if you should add it to Count, right?
Seems to me Count is an extra step - however small.

As vbaInet said -- reduce the number of records to compare by removing duplicates from each table first. Seems you have already reduced the number of Cancellation records by 75%...

 

[vbaInet]

Once you have a distinct set of policy numbers on both sides, the match will almost be one-to-one, and I use the word "almost" loosely. From that point, you can (in another query) join the matches with an INNER JOIN against the table with the duplicate and longer formatted policy numbers.

Makes sense?

 

[aman]

Thanks Guys. My manager wants to keep track of how many duplicate policy numbers for each so there is no point using distinct to remove duplication.

Also I have to write vba code now that will check each policy number from TBLCancellationModes using count function to see if it exists in AnnutiesTBL and if yes then store that policy number details in table "KLMPolicies" other wise move to next record.

Guys I really appreciate your help but will the above VBA code run much quicker than the that query?? And also I will be using following loops one by one:

1. For Loop from 1 to 10000 so that only first 10,000 policynumber from TBLcancellationModes will be checked against AnnutiesTBL using count to see if its present and if yes then store the details in the table.

2. then manually I will change the loop from 10001 to 20000 and so on....

Hope u guys will help me in this.

Many thanks

 

[vbaInet]

You don't understand what was explained.

Table A to match Table B

Table A has the following policy numbers:

1234
1234
1879
6766
1234

Table B also has the following duplicate policy numbers:

12340000
12340000
67660000
12340000
45900000
98000000

Get the unique/distinct policy numbers in both tables and match them using the queries already given. You get:

TableA_match	   TableB_match
------------	   ------------
1234	   	   12340000
6766	       	   67660000

Use the query above and join it (i.e. using an INNER JOIN) against the original TableB which has duplicate policy numbers. You get:

TableB_match	   TableB
------------	   --------
12340000	   12340000
12340000	   12340000
67660000	   67660000
12340000	   12340000

You follow?

 

[aman]

Thanks VBAnet, I got it. Now I have two tables CancellationModes contain 344835 records and AnnutiesTBLTest contains 71426 records.

Now after this, which query I need to run? Thanks for your help so far.

 

[vbaInet]

I am no longer keen to provide further help since you feel it's acceptable to create another thread whilst you're being helped.
http://www.access-programmers.co.uk/forums/showthread.php?t=279257

 

[aman]

VBAnet, The other thread is about writing VbA code to find matching records. In this thread we are writing query to do the same. No problem. I will close the other thread.
I am writing the following query after creating two tables with distinct policy numbers but its taking long time as well to run.

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]))