Multi-table wildcard query

[dearsina]

I have two tables in Access with company data, like company name, address, contact, etc.

One of the tables has complete company names, like "Random Business Limited" or "Huge Corporation Plc", the second has only shorter names like "Random Business" or "Huge Corporation".

I wish to set up a query which will recognise these fields as matches, something like if table1.a = table2.a* show table1.a, with the wildcard being the key symbol of course.

Am I making sense? Can it be done?

Thanks in advance,

sina
london

 

[sfreeman@co.mer]

maybe not

Maybe we can't get there from here

Assuming tables with sample data:

Table1
CoName
Huge Company LLC
Big Mucks Incorporated
Even Bigger Mucks, Inc.

Table2
CoName
Huge Company
Big Mucks
Even Bigger Mucks

Create a Union Query to get all the data into one table:
Select *
From Table1
UNION select *
from Table2;

Then, my first thought was to run a standard find duplicates query, i.e.:
SELECT PrimaryUnionQuery.CoName, PrimaryUnionQuery.CoAddress
FROM PrimaryUnionQuery
WHERE (((PrimaryUnionQuery.CoName) In (SELECT [CoName] FROM [PrimaryUnionQuery] As Tmp GROUP BY [CoName] HAVING Count(*)>1 )))
ORDER BY PrimaryUnionQuery.CoName;

BUT, there is no way to incorporate the wildcards (at least not as far as I can see).

So, to use wildcards, you would have to know the similarities for each of the matching records, which would probably create a query as long as your entire table, i.e.

SELECT PrimaryUnionQuery.CoName
FROM PrimaryUnionQuery
WHERE (((PrimaryUnionQuery.CoName) Like "Big*")) OR (((PrimaryUnionQuery.CoName) Like "Huge*")) 'etc. etc. etc. etc.
ORDER BY PrimaryUnionQuery.CoName;

But then, it is still not providing you with much usable info.

Lastly, and this is the best I can come up with, to show all matching records, if you know of a common criteria, such as the first 5 letters are the same in each record in each of the tables, oh say, like the sample data above, then:

SELECT Table1.CoName, Table2.CoName
FROM Table1 INNER JOIN Table2 ON Table1.CoName Like Left(Table2.CoName,5)& "*";

'note the number 5 is the number of left most characvters that will be used as criteria - adjust as necessary

Just a suggestion, but when you finally get a clear match on all of the records, use a forgeign key field so that you don't have two sets of names. For example, have the second table contain the CompanyID value in the name field referencing the first table.

Good luck..

 

[dearsina]

Hi, and thanks for your suggestions. You are absolutely right that the first two suggestions have problems.

SELECT Table1.CoName, Table2.CoName
FROM Table1 INNER JOIN Table2 ON Table1.CoName Like Left(Table2.CoName,5)& "*";

The last one though, was interesting (see above), and I gave it a go, only to discover that there are companies as small as "Oxfam" and as large as "Marketlink Marketing Communications Ltd" (Actual data). So the number 5 (or whatever its set to be) becomes either too big or too small.

Then a thing occured to me, what about exchanging the static value of 5 with a dynamic value of the length of the table2.CoName which its trying to match? I tried the following query:

SELECT [Viatel data].COMPANY, [Our data].company
FROM [Our data] INNER JOIN [Viatel data]
ON [Viatel data].COMPANY LIKE Left([Our data].company,Len([Our data].Company)) & "*";

([Viatel data] being the table with the longer names, and [our data] the shorter)

But that just killed the query. I don't know if Len() is an ideal function for this, or if there is a way of rewriting the above line to make it run reasonably ok?

I have a feeling there is a way to solve this. Call it a hunch, call it geektuision, call it Susan if that makes you happy, but I think it can be done..!


sina
london

 

[Dwight]

My suggestion is that you spend your energy normalizing the data in the tables so that you do not have to resort to complicated queries.

 

[dearsina]

Dwight, I'm not keeping the records, I'm just comparing them; and there is a lot of it, so normalizing it isn't really an option. It would definetily have been my suggestion too, if I was to keep the data.

sina
london

 

[dearsina]

If you're reading this in the future (or today rather, in the future when thinking of when I wrote this post) thinking you have the solution, please please write a couple of lines. Althought this particular project is over (I wasn't able to produce this crutial rapport) I would like to know how to do this, because I'm sure there is a way, I doubt I'm the only one that has ever had this problem!

sina
london