LIKE Query

[Ranjith Kurian]

i have two tables as shown below

Table 1

• 
  Customer Name
  Bank of New York
  DenMark
  
  Table 2 [Source]
  CustomerName
  Bank of New York The
  DenMark LPC
  DenMark
  Bank of New
  
  
  
  Below is the query i hv built, but the result is not to my expection.
  
  SELECT [tbl List].[Customer Name], [tbl Source].CustomerName
  FROM [tbl List], [tbl Source]
  WHERE [tbl List].[Customer Name] LIKE [tbl Source].CustomerName;
  
  In the above code i have used LIKE funtion which pulls only the similar name
  but i need my query to pull allmost similar name.
  
  Result expected:
  CustomerName
  Bank of New York The
  DenMark LPC
  DenMark
  Bank of New

 

[Jerry Whittle]

I doubt that you are going to have very much luck. In fact what you are
already getting could be wrong. What happens if you have more than one "Jim
Jones"?

You are probably going to need human eyes on this one and hopefully more
information such as addresses and account numbers.

 

[John Spencer]

No wild cards there, so you might as well use the equal operator instead of
the like operator.

Perhaps the following is what you are looking for. One problem is that if the
customer name is ever null or a zero-length string, you will end up returning
every record.

SELECT [tbl List].[Customer Name], [tbl Source].CustomerName
FROM [tbl List], [tbl Source]
WHERE [tbl Source].CustomerName LIKE [tbl List].[Customer Name] & "*"
OR [tbl List].[Customer Name] Like [tbl Source].CustomerName

One problem is that if the customer name is ever null or a zero-length string,
you will end up returning every record. So you might want to expand that to

SELECT [tbl List].[Customer Name], [tbl Source].CustomerName
FROM [tbl List], [tbl Source]
WHERE
([tbl Source].CustomerName LIKE [tbl List].[Customer Name] & "*"
AND [tbl List].[Customer Name] is not null)
OR
([tbl List].[Customer Name] Like [tbl Source].CustomerName & "*"
AND [tbl Source].CustomerName is Not Null)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County