Find Similar records in Two Tables (1 Viewer)

[Jaymus]

Hi, I need some help with putting together a results that involves two different tables.

Say Both Tables have a common Field - Company Name

Table 1 has a less records with only one field - Company Name
Table 2 has a longer list with TWO fields - Company Name and City

The Company Names are similar in both tables
but I would like to display the results with Company name from BOTH Tables and Any cities associated with the name from table 1

Problem is that ie AT&T in table 1 won't link up to AT&T International in Table 2.

I know I can use the Like * wildcard but how do I link the two tables with the criteria of table 1 to find similar companies

Tables1

Company Name
AT&T

Table2

Company Name City
AT&T International Palo Alto
AT&T International London
AT&T Coporate London

Sorry if I sound very confusing, but my main questions is I suppose:


How do I write a query to find Similar * Company Names between two Tables

Thank you for any help you can provide.

Jaymus




[This message has been edited by Jaymus (edited 07-24-2001).]

 

[pcs]

Jaymus,

if all you are trying to do is join the two tables if company name always starts with 'AT&T', that is do-able...

but if,
--------------
The Company Names are SIMILAR in both tables
--------------
you have a really big job! 'similar' would require a parsing algorithm that is best left to those who write internet 'search engines'.

hth,
al

 

[SunWuKung]

I am not a big comp wizard. When I had to look for similar sentences in two fields of a query I wrote a function that comes up with a similarity score for the each possible pair, ordered the pairs according to that sore and than manually decided on the top scorers. This was very far from being fail safe thogh. You have to consider the huge number of possible comparisons even if you only have a 1000 record in both fields.
Also think about how similar
AT&T International London and
AT&T Coporate London
are in terms of their character sequence.

If you need I can send you the function I wrote although I would be ashamed to admit publicly how badly I do programming. I am sure others could come up with much more sophisticated algorithms.

 

[Jaymus]

Sure, please send the function to me. Thanks.

Actually I only need to compare the first word is good enough to be similar.(In Company Name)

Say, in Table 1 has AT&T, I just want to bring up anything that its AT&T* in table 2's Company Name, and bring up other fields in Table 2 (Which would be City)

I already have Table 1 and 2's Company name broken up into 1st Word and Next Word, using LEFT$, RIGHT$ and InStr() etc.

I just need to a way to tell access to keep taken records from Table 1 and compare to Table to for anything that is similar in Company name in Table 2!

I guess if that's too complicated, I'll just join the First Word only.

Thanks all!

Jaymus

 

[dobygee]

I am also interested in your similarity score function. Can you submit it here or send it to me as well? Thx!

 

[Chrismeli]

Hello! I see it is solved but which was the solution ? ( 9 years after last post )

 

[The_Doc_Man]

Unfortunately, Chrismeli, the original poster didn't file an answer and I have not recently seen any of the members who participated in the original thread. Further, that post from 9 years ago was an inquiry that didn't get an answer, so you are REALLY looking at asking about something from 18 years ago.

My best advice is to file a separate new thread and just point to this thread via hyperlink. I don't have a similarity subroutine for you, though.

 

[Chrismeli]

Hey The Doc Man! Thank you for replying to my post! Well I ll maybe do that because I ve been looking for the same thing and cant find it nowhere. Tommorow I ll go back to the office and check something that i have on my mind. It has to do with left function and left join! Will post if it works!

 

[Chrismeli]

Sooo The solution is the following Table1 has some names. Lets say john margaret etc etc . What i did is a created a query with the left function so i can check the first letters of the name i am interested in looking on table 2

SELECT Left([Jarred],4) AS Expr1
FROM Table1;

For table 2 i did exactly the same, creating a query with the left function (AND 4 FOUR LETTERS ) and i put an extra field on table 2 (for example it can be address, dob, nickname , whatever you have

SELECT Left([Jarred],4) AS Expr1, Table2.Field1
FROM Table2;

Then i created a relation between query 1 and 2 based on the Expr1 and after that i went ahead and created a form of query one and in that form a created a subform and selected Query2, put both the fields ,selected the second option "show Query2 for each record in QUery1 using expr1", hit next and finish.
Voila

 

[The_Doc_Man]

If that works for you, then great. If all you need is the first four letters, then what you did sounds like it is in the right direction. It is also an example of "divide and conquer." You in essence separated out the problem of isolating the critical part of the record from the problem of matching critical parts. From the description, though, may I suggest that you read up on the "DISTINCT" keyword as a way to minimize what we call a "cross-product" result? You would only need it on table 1, I think. Just food for thought, please take it as such.