Isolating Records from 2 Tables that do not Match

[wcrimi]

I have 2 large tables (approx 50k records each). They mostly match on my key, but one table has some record keys that the other does not.

What I want to do is match them on their keys (easy enough), but instead of getting a list of all the matches, I want a list of the mismatches (extra records) on that one table. Is there a reasonably easy query technique for doing that?

[ranman256]

Make an OUTER join:
make the normal query with the join to get the matches (inner join)
bring down the key from both tables into the grid
dbl-click the join line
set to: show ALL records from table1, Some recs from table2
under criteria for key.table2, set NULL

this will show all recs in table1, and the missing in table2

then reverse the join (and criteria) to see all recs in table2 and missing in table1

[orange]

Access provides a Query Wizard. One of the options is Unmatched Query.
It provides a dialog to assist you to create the query and run it.




General format of unmatched query as Ranman suggested.

'in this example, I'm looking for Animals that have not been captured.
SELECT Animal.AName
FROM Animal
LEFT JOIN AnimalCapture ON Animal.[AnimalId] = AnimalCapture.[AnimalId]
WHERE (((AnimalCapture.AnimalId) IS NULL));

Good luck.

[wcrimi]

Thank you.

[orange]

You are welcome. There are other options in the Query Wizard also that can be useful in certain cases.

Good luck.