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.Company

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.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] & "*"));



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

I have in my WorkBook.Company field "FirstEnergy Inc" and in my Customers.Company field I have "FirstEnergy" 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.Authorname, WorkBook.FirstName, WorkBook.LastName, WorkBook.Title, WorkBook.Email, WorkBook.Company, WorkBook.[Lead Status]

FROM WorkBook, Customers

WHERE Customers.Company 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] & "*";
 

[RiskWatch]

ADezii,

We are painfully close.

Your last post just produces a copy of the WorkBook and has removed the 4 records it was identifying before. So, roughly the opposite effect.

To summarize:

Your initial code successfully identified the matches between [Customers] and [WorkBook], including wildcards and filtered them to the top of the table. However, it included a copy of the entire [WorkBook] table below the results.


Your second code removed what we had identified previously and left only the copy of the [WorkBook] table.

Screenshot of the new results included below.

(Thank you for all of your help, I'm amazed we've gotten this far and will be blown away if we can get it working.)

[RiskWatch]

http://imgur.com/a/nTLsX

[RiskWatch]

[ADezii]

I honestly need more info than what the Image is showing.

[RiskWatch]

ADezii,

Give me a few moments to build out an example that I can share, I'm working with sensitive information. I'll attach some additional links in just a few minutes.

[RiskWatch]

There must be some "user" error or something I have overlooked.

When I started to use your first solution with test data, it works perfectly.

I will have to take a closer look why it will not work with my real data.

Thank you for all your help. I will mark your first response as the correct solution and create a new post if and when I identify the issue with my data.