Make Table Query on Like Fields

[depawl]

Hello.
I am developing a Make Table Query from 2 tables, one of which has an "Employee Name" field (lastname,firstname) and the other table has separate fields for LastName and FirstName. I've been able to accomplish almost what I need by:
WHERE ((([Table 1].[EMPLOYEE NAME]) Like [Table 2].[LAST NAME] & "*"));
which works fine except when there are 2 employees having the same last name, then it generates duplicates. I suspect there must be a way to do this by incorporating the first name field in the sql statement but that's beyond my ability. Any help would be appreciated. I realize that names are not good things to base a query on, but the 2 existing tables have been preset and populated by others and I don't really have the capability to change them.
Thanks.

 

[sxschech]

This won't handle exceptions, but if data in the table looks like "Lastname, FirstName" then you could do

WHERE [Table 1].[EMPLOYEE NAME] = [Table 2].[LAST NAME] & ", " &[Table 2].[FIRST NAME];

 

[depawl]

OK, that seems to work fine.
Thanks.

 

[depawl]

Having solved that as per above, I now need to find the employees that exist in Table 1 but not in Table 2 (or vice versa). So using my expression:
WHERE ((([Table 1].[EMPLOYEE NAME]) Like ([Table 2].[LAST NAME] & ", " & [Table 2].[FIRST NAME] & "*")));
And changing the "Like" statement to "Not Like"
returns all of the records numerous times.
I've tried various permutations of LEFT JOINS, RIGHT JOINS, etc. with no luck. Any suggestions?
Thanks.