Unmatched Query

[depawl]

Hello all. I need to design an unmatched query on 2 tables to identify employees names that exist in 1 table but not the other (or names are misspelled, etc.). The tables exist in on a server and I do not have the ability to change either.
Table 1 has the following structure:
EMPLOYEE NAME: Doe, John ID-12345
Table 2 has the following structure:
LAST NAME: Doe
FIRST NAME: John
Since the field names are not equal, the unmatched query wizard (as far as I know) will not work. I've tried using "NOT LIKE", LEFT JOINS, RIGHT JOINS, NUll Values, etc. in the SQL but nothing has worked as yet.
Here's the SQL I've been working with but this returns all values (haven't figured out why):
SELECT DISTINCT [Table 1].[EMPLOYEE NAME]
FROM [Table 1], Table 2
WHERE ((([Table 1].[EMPLOYEE NAME]) Not Like "([Table 2].[LAST NAME]*"));
Thanks.

 

[plog]

First, this query or method isn't the way to accomplish what you want. I'll explain why your query doesn't work, but even if it did, it wouldn't be the way to go about this.

Your query returns all values because your WHERE clause will be True for every record in Table 1. Its actually worse than that--remove the DISTINCT from it and your query will return almost [Table1 record count] * [Table 2 record count] results. This is because you haven't really joined your tables. Let's suppose this is your data:

Table1
[EMPLOYEE NAME]
David Smith
Terry Jones

Table2
[LAST NAME]
Jones
Smith

Your query will show all the records in Table1 because 'Terry Jones' does not contain 'Smith' and 'David Smith' does not contain 'Jones'. It doesn't matter that 'Terry Jones' contains 'Jones' because it is also being compared to that 'Smith' record in Table 2 and it passes your criteria and thus makes it into your results.

If you look at my sample data you will see why this isn't the way to go. You need to match on as much data as you have to make sure you are matching correctly. Matching on just last name is a bad idea--you will kick out every Smith and Jones. My suggestion for comparing your data is to create a query on Table 1 that extracts out firt and last name. You would then use that query in another query to compare those fields to Table2. Don't try this all at once--create a sub-query on Table 1 then try and match.

 

[ExoDus84]

Unmatched queries are simple if your table structure is sound. The problem is your data is not structured very well. You'd want to have an identical field name / data type for both tables. Such as Employee ID in table 1 is equivalent to employee ID in table 2. You need something to be able to match it with.