Finding differences btwn 2 tables 2

[cdgeer]

I am trying to run a query to find the differences between last name in one table and last name in another. Also first name differences between the two. Here is the SQL. I can't figure out why it's not working. The query returns all as being different.

SELECT DISTINCT MACTABLE.[LAST NAME], MACTABLE.[FIRST NAME], MACTABLE.ROLE
FROM MACTABLE, Lawson
WHERE (((MACTABLE.[LAST NAME])<>Lawson.Last)) Or (((MACTABLE.[FIRST NAME])<>Lawson.First));

 

[Golom]

What are you expecting exactly?

Obviously, if each table has (for example) 10 names then, barring duplicates, at least nine of the names in Mactable will be "different" than nine of the names in Lawson.

Are you looking for names that exist in one table but not in the other?

 

[cdgeer]

Yes. There are many duplicates but I want to find the names in Mactable that don't match any of the ones in Lawson.

For example: Mactable Lawson
Last Name First Name Last First

Adams Stephanie A. Adams Steph.A.
Baker Wendy Baker Wendy
Baker Sam Baker Samuel
Baker Frank Baker Frank L.

 

[Golom]

Try this

SELECT DISTINCT M.[LAST NAME], M.[FIRST NAME], M.ROLE

FROM MACTABLE As M

WHERE M.[LAST NAME] & M.[FIRST NAME] NOT IN 
      (Select Last & First From Lawson)

Which (with your sample data) should give
[tt]
Last Name First Name
Adams Stephanie A.
Baker Sam
Baker Frank
[/tt]

 

[PHV]

Why not simply use the unmatched query wizard ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

Golom,

If I put in:

SELECT DISTINCT M.[LAST NAME], M.[FIRST NAME]
FROM MACTABLE As M
WHERE M.[LAST NAME] and M.[FIRST NAME] NOT IN 
(SELECT [Last name], [First name] From Lawson)

Access yells at me and says "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

(learning exercise for me)

 

[Golom]

Note that I said

WHERE M.[LAST NAME] [red][b]&[/b][/red] M.[FIRST NAME] NOT IN 
(SELECT [Last name] [red][b]&[/b][/red] [First name] From Lawson)

"&" and "and" are not at all the same things. "&" concatenates fields while "and" produces a logical result (True or False).

The comma in the sub-select returns two fields while an "&" operator returns one field composed of two fields concatenated.

 

[BigRed1212]

Aha! I had no idea. Cool. Very neat. Thanks.

 

[cdgeer]

Thank you so much Golom!!!

It works perfectly!

 

[PHV]

Another way without subquery:

SELECT M.[LAST NAME], M.[FIRST NAME]
FROM MACTABLE AS M LEFT JOIN Lawson AS L ON M.[LAST NAME]=L.Last AND M.[FIRST NAME]=L.First
WHERE L.Last Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886