Match and then unmatched

[dakpluto]

I need to do a query that first matches two common fields between two tables, and then display a second field between the two that does not match.



Example:

Number	Name
1	Johnson
2	Kerry
3	Hancock
4	Jefferson
5	Harris
6	O'Neal
7	Larkin
8	Millsap
9	Harvey
10	Harrison

Number	Name
1	Johnson
2	Howard
3	Hancock
4	Jefferson
5	Harrison
6	O'Neal
7	Larkin
8	James
9	Harvey
10	Harold

I would need the query to match the records by the number column first, and then display the records that do not match the name column. So in this example, it should return numbers 2, 5, 8, 10. I cannot just simply run an unmatched on the two name columns, because as you can tell, 10 would not kick because the last name is also in 5 in the other table.

[JoeM]

Sure, you can do an Unmatched query between the two. You just need to include BOTH fields in your Join.
Then it will only return records where BOTH fields do not match (which I think is what you are looking for).

[DepricatedZero]

Perhaps this is a shortcoming on my part, but this seems like two separate queries to me.

[dakpluto]

No, I need to match the first field. Here is the issue, somebody screwed up a bunch names in one database. So I have to match the account number from a master list to the screwed up database. Then show those matched records where the last names do not match so i can repair them. So I have to MATCH, then do an unmatched. Like my example above, it first matches 1 to 1, then looks at the last name. If they match, ignore and move on. If the last names do not match, output it.

[dakpluto]

Perhaps this is a shortcoming on my part, but this seems like two separate queries to me.

Not opposed if it requires more, but I need to make sure the records retain that matching when it check the unmatched. Otherwise, like in my example above, Account 10 in the first table would not output because the last name Harrison is present in the messed up table and wouldn't count as unmatched.

[JoeM]

I don't think you are understanding what I am telling you. The steps that I mentioned will return records 2,5,8, and 10. That is what you want, right?

The SQL code of that unmatched query might look something like:

SELECT Table1.*
FROM Table1 
LEFT JOIN Table2 
ON Table1.Name = Table2.Name 
AND Table1.Number = Table2.Number
WHERE Table2.Number Is Null;

[amrut]

This link may be helpful http://allenbrowne.com/subquery-01.html

[dakpluto]

I don't think you are understanding what I am telling you. The steps that I mentioned will return records 2,5,8, and 10. That is what you want, right?

The SQL code of that unmatched query might look something like:

SELECT Table1.*
FROM Table1 
LEFT JOIN Table2 
ON Table1.Name = Table2.Name 
AND Table1.Number = Table2.Number
WHERE Table2.Number Is Null;

ok, access kinda screwed it up with their default joins, but this worked well, thank you.

[JoeM]

If you were using the Access Unmatched Query Wizard, they will only do one join for you, so you usually need to go in and manually add the second join.

[dakpluto]

I added the second join manually in design but the SQL code wasn't working with it.