Query two tables and duplicates with wildcards

[RiskWatch]

Hello to all.

I am comparing two tables [Customers] and [Workbook]

I am trying to identify, in the table [WorkBook] places where the "Company" field is similar or the same to the Customers.Compa ny

I am now able to successfully identify exact matches, but what I need is to place wildcards on either side of my search function.

I have the following SQL statement

SELECT WorkBook.ID, WorkBook.Authorname, WorkBook.FirstName, WorkBook.LastName, WorkBook.Title, WorkBook.Email, WorkBook.Company, WorkBook.[Lead Status]
 
FROM WorkBook LEFT JOIN Customers ON WorkBook.[Company] = Customers.[Company]
 
WHERE (((Customers.Company) Like [WorkBook.Company] & "*"));

The statement above will generate a list of matches, but only exact matches. I need to be able to identify the same thing but with variations on either side.

I believe the issue is in my last line of code but I have not been able to figure it out.

This SQL statement successfully identifies the exact matches between WorkBook and Customers but I want to use a wildcard operator like % or * on either side of the company name and have those show up as well.

That is to say, imagine I have in my list of customers "Microsoft" ; I want to have wildcards so that if in the workbook table I had "Microsoft Inc" or "Super Microsoft" or "Super Microsoft Inc" they would also be identified.

Thank you for any help and sorry if this is a simple request.

===

[PhilOfWalton]

Try

WHERE (((Customers.Company) Like "*" & [WorkBook.Company] & "*"));

Phil

[RiskWatch]

Hi Phil,

I tried it as follows:

SELECT WorkBook.ID, WorkBook.Author name, WorkBook.FirstN ame, WorkBook.LastNa me, WorkBook.Title, WorkBook.Email, WorkBook.Compan y, WorkBook.[Lead Status]

FROM WorkBook LEFT JOIN Customers ON WorkBook.[Company] = Customers.[Company]

WHERE (((Customers.Co mpany) Like "*" & [WorkBook.Compan y] & "*"));



This produced the same results as the query I posted above.

I have in my WorkBook.Compan y field "FirstEnerg y Inc" and in my Customers.Compa ny field I have "FirstEnerg y" and this query did not pull the record from WorkBook.

It should be noted that with your adjustment it also pulls the exact matches, but not the variations (wildcards) ; This is exactly the error I encountered with my original query.

[ADezii]

Give this a shot, don't think the Tables can be joined:

SELECT WorkBook.ID, WorkBook.Authorname, WorkBook.FirstName, WorkBook.LastName, WorkBook.Title, WorkBook.Email, WorkBook.Company, WorkBook.[Lead Status]
FROM WorkBook, Customers
WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";
 

[RiskWatch]

ADezii,

Excellent! Your solution does in fact pull the items like I want it to. It even seems to sort them at the top of the list.

The only issue I have is that it also includes ALL of the records from WorkBook below. (So now I have duplicate records and records I didn't want identified at all.)

I will post a screen shot for clarification, but can only show the IDs as some of the other data is sensitive.

[RiskWatch]

http://imgur.com/a/T09F5

[RiskWatch]

[RiskWatch]

If you follow the link to the image, you'll see that the records at the top 12,13,14 and 11 are the correct records that I wanted to identify with this query.

Unfortunately having all of the records from WorkBook duplicated below it is ruining what I was trying to achieve.

Is there a way to filter out 1-14 and only keep the 12,13,14,11 using a single query? Will I have to do another query to remove duplicates?

[RiskWatch]

I tried reversing the last line as such:

SELECT WorkBook.ID, WorkBook.Author name, WorkBook.FirstN ame, WorkBook.LastNa me, WorkBook.Title, WorkBook.Email, WorkBook.Compan y, WorkBook.[Lead Status]

FROM WorkBook, Customers

WHERE Customers.Compa ny Like "*" & [WorkBook].[Company] & "*";

This brings back being unable to identify wildcards but pulling exact matches.

[ADezii]

Try using the DISTINCT Predicate:

SELECT DISTINCT WorkBook.ID, WorkBook.Authorname, WorkBook.FirstName, WorkBook.LastName, WorkBook.Title, WorkBook.Email, WorkBook.Company, WorkBook.[Lead Status]
FROM WorkBook, Customers
WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";