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

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

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.

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! http://www.access-programmers.co.uk/ubb/smile.gif

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

Thanks all!

Jaymus

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