LIKE Query

  • Thread starter Thread starter Ranjith Kurian
  • Start date Start date
R

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
 
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.
 
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
 
Back
Top