Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 07-24-2001, 04:36 PM   #1
Jaymus
Guest
 
Posts: n/a
Find Similar records in Two Tables

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

  Reply With Quote
Old 07-24-2001, 06:37 PM   #2
pcs
Senior Member
 
pcs's Avatar
 
Join Date: May 2001
Location: USA,Indiana,Technology Backwater
Posts: 398
Thanks: 0
Thanked 0 Times in 0 Posts
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
pcs is offline   Reply With Quote
Old 07-25-2001, 06:36 AM   #3
SunWuKung
Registered User
 
Join Date: Jun 2001
Location: Budapest
Posts: 172
Thanks: 0
Thanked 0 Times in 0 Posts
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.

SunWuKung is offline   Reply With Quote
Old 07-25-2001, 09:52 AM   #4
Jaymus
Guest
 
Posts: n/a
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
  Reply With Quote
Old 11-23-2010, 01:41 PM   #5
dobygee
Newly Registered User
 
Join Date: Nov 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
dobygee is on a distinguished road
Re: Find Similar records in Two Tables

I am also interested in your similarity score function. Can you submit it here or send it to me as well? Thx!
dobygee is offline   Reply With Quote
Old 08-25-2019, 12:53 PM   #6
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Find Similar records in Two Tables

Hello! I see it is solved but which was the solution ? ( 9 years after last post )
Chrismeli is offline   Reply With Quote
Old 08-25-2019, 01:44 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Find Similar records in Two Tables

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-25-2019, 02:48 PM   #8
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Find Similar records in Two Tables

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 is offline   Reply With Quote
Old 08-25-2019, 05:32 PM   #9
Chrismeli
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Chrismeli is on a distinguished road
Re: Find Similar records in Two Tables

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

Code:
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
Code:
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
Chrismeli is offline   Reply With Quote
Old 08-25-2019, 06:20 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Find Similar records in Two Tables

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 06:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World