Find subtle differences

Two Tables to compare:  "Old Data" and "New Data"
I need to query and find any difference at all in the following fields:
Full Name, Grade, Abbreviation, Home, TechID.

Key field is "Full Name"

Is there a single query that will show me any difference in any of those fields?

I would like to show the results from table "New Data", the old doesn't matter.
TIgerVAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query

SELECT [New Data].[Full name], [New Data].Grade, [New Data].Abbreviation, [New Data].Home, [New Data].TechID
FROM [New Data] LEFT JOIN [Old Data] ON ([New Data].TechID = [Old Data].TechID) AND ([New Data].[Full name] = [Old Data].[Full name]) AND ([New Data].Grade = [Old Data].Grade) AND ([New Data].Abbreviation = [Old Data].Abbreviation) AND ([New Data].Home = [Old Data].Home)
WHERE ((([Old Data].[Full name]) Is Null) AND (([Old Data].Grade) Is Null) AND (([Old Data].Abbreviation) Is Null) AND (([Old Data].Home) Is Null) AND (([Old Data].TechID) Is Null));


or use the query wizard > Find Unmatched query wizard
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Key field is "Full Name"
Bad choice, as there can be more than one person with the same name, i.e. John Smith, Jane Doe, Paddy O'Flavo, and in those cases the name is not unique.

>I need to query and find any difference ... Full Name
>Key field is "Full Name"
You can have one or the other, but not both, as SQL needs to know how to join these two tables, and if you're using Full Name then it looks for rows where the two values are the same, and has no way of knowing if different rows are a legitimate difference (Jim Horn vs. James Horn), or a difference to ignor (Danny Wareham vs. Miriam Bizup).

>Is there a single query that will show me any difference in any of those fields?
Something like..

SELECT n.[Full Name], n.Grade, n.Abbreviation, n.Home, n.TechID
FROM [New Data] n
   INNER JOIN [Old Data] o ON n.[Full Name] = o.[Full Name]
WHERE n.Grade <> o.Grade OR
   n.Abbreviation <> o.Abbreviation OR
   n.Home <> o.Home OR
   n.TechID <> o.TechID
0
 
TIgerVAuthor Commented:
Find unmatched was only giving me one column's difference.  However the SQL code above worked fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.