Query - Find Similar

[Vcscwi]

Hi All,

Need to create a query which pulls from 2 tables. Table "A" has custname and Table "B" has clientname. I want to create a query to get a list of all A!custname and the B!clientname that is an exact match or "like" match.

Is there an easy way to do this?

Thanks

 

[Bugget]

Try something like:

SELECT A.CustName, B.ClientName
FROM A, B
WHERE (A.CustName Like B.ClientName)

That should grab them all. If its not getting everything you could try grabbing custname, using a left function on it and appending a wildcard character to it to get even more results with less precision. Hollar if you need an example!

- Tom

 

[Vcscwi]

Here is an example of what I want to see.
A!Custname has "Abbott Company, The"
B!CleintName has "Abbott Company (The)" so far anything I try doesn't match these two companies together.

Any other ideas?

 

[Bugget]

Thats a tough one. The issue here is precision as I stated earlier. If you cut off everything after the comma and then did a like, it would find the record. The problem is that it will find anything else that starts with "Abbot Company". The level of precision is something you need to decide. Here is a code idea to show you what I mean:

SELECT A.CustName, B.ClientName
FROM A, B
WHERE (A.CustName Like '" & Left(B.ClientName, 10) & "*')"

This will probably match that case for you, the problem is that it always truncates your search criteria after the tenth place and throws in a wildcard in order to match anything that has the first ten characters in commmon with your clientname.

Another way to do this would be to get a recordset of everything in A.CustName and a second with everything in B.ClientName and do the comparisons in a loop yourself. This is much slower but it will allow you to define how you wish to compare things. Otherwise, I don't think you can do something that will match that example in only SQL.

- Tom

 

[PHV]

You may consider SOUNDEX

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bugget]

Last I checked SOUNDEX was not part of SQL and was only supported on specific Databases. Depending on his DB he may be able to find a similar function though.