Finding unmatched records between 2 tables in MS Access

Just use left join from the table with the bigger recordcount ...the null ones should be the unmatched

Can you show the SQL you're using to create the tables?

SELECT LEABSF.Client, LEABSF.Date, LEABSF.Cond, LEABSF.TypeFZ, LEABSF.AgeRange, LEABSF.Site
FROM LEABSF LEFT JOIN LEABSZ ON LEABSF.[AgeRange] = LEABSZ.[AgeRange]
WHERE (((LEABSZ.AgeRange) Is Null));

Returned no results

SELECT LEABSZ.Client, LEABSZ.Date, LEABSZ.Cond, LEABSZ.TypeFZ, LEABSZ.AgeRange, LEABSZ.Site
FROM LEABSZ LEFT JOIN LEABSF ON LEABSZ.[AgeRange] = LEABSF.[AgeRange]
WHERE (((LEABSF.AgeRange) Is Null));

Returned no results

SELECT LEABSZ.Client, LEABSZ.Date, LEABSZ.Cond, LEABSZ.TypeFZ, LEABSZ.AgeRange, LEABSZ.Site
FROM LEABSZ LEFT JOIN LEABSF ON (LEABSZ.Site = LEABSF.Site) AND (LEABSZ.AgeRange = LEABSF.AgeRange) AND (LEABSZ.TypeFZ = LEABSF.TypeFZ) AND (LEABSZ.Cond = LEABSF.Cond) AND (LEABSZ.Date = LEABSF.Date) AND (LEABSZ.Client = LEABSF.Client)
WHERE (((LEABSF.AgeRange) Is Null));

Returned 166212 records (all records in LEABSF)

SELECT LEABSF.Client, LEABSF.Date, LEABSF.Cond, LEABSF.TypeFZ, LEABSF.AgeRange, LEABSF.Site
FROM LEABSF LEFT JOIN LEABSZ ON (LEABSF.Client = LEABSZ.Client) AND (LEABSF.Date = LEABSZ.Date) AND (LEABSF.Cond = LEABSZ.Cond) AND (LEABSF.TypeFZ = LEABSZ.TypeFZ) AND (LEABSF.AgeRange = LEABSZ.AgeRange) AND (LEABSF.Site = LEABSZ.Site)
WHERE (((LEABSZ.AgeRange) Is Null));

Returned 166174 records (all records in LEABSZ)

When you using AND all criteria must be met.

You may need to use the left join in not (conditions and

I.e the last query add a not in front of the entire group.

Logic in simplified form
Column A.           Column B.                Not.                 Direct
Mismatch.           Mismatch.                True.                False
Mismatch.           Match                        True.               False
Match.                  Mismatch.                True.               False
Match.                  Match                        False.              True

Point is that you will get results from rows where one of the columns does not match while getting no info where all match.

The issue with an all inclusive query is that you can not tell, rely on null

Using the results I would include the columns from both tables.
Using a script to run through comparing data in specifuc columns

First of all, you say there SHOULD be the same number of rows for each [TypeFZ] column after you run a query to split the records into two tables.  Judging from the values in the [TypeFZ] column and the name of your two tables, I suspect you are trying to put all the "ABSF" into the "ABSF" table, and the "ABSZ" into the "ABSZ" table - and you're expecting there to be the same number of "ABSF" records as there are "ABSZ" records.

But you are not getting the same number of records.  Let's assume that this is due to the data and not your splitting logic.  You want to run "unmatched" queries, but they are defying your logic.  Stop for a moment and think about what you want an empty field to signify.  Nulls have a different need when you are comparing field content.  Do you want a linked field that is Null (blank) in one table to equal a Null (blank) in the other table?  Here's the big issue I came across when designing a comparison system for a major nationwide insurance company - A Null in one table does NOT equal a Null in another table, so if you want a blank to equal a blank, you need to fill the null with an empty string (or something - just make sure you fill all nulls with the same thing).  In other words, Null <> Null, but "" = "".

Once you make sure all the columns you are linking have empty strings instead of nulls, you'll get better results.  If there is as much as a single no-match on ANY of the numerous linked columns, you won't get a record from the "unmatched" table.  Any record on the "all records" side of a left-hand join will result in the "Null" check column on the  "unmatched" table column being the only Null you'll need to check for.

Also, make sure there are NO differences in your two table designs, and that the data, after splitting has no leading or trailing spaces or other "invisible" characters that you can't see.  Computers can see the differences between Nulls, empty strings, spaces, carriage returns, line feeds, etc., even though you can't.  So, if it looks the same to you, but the computer is saying it isn't, I'd believe the computer....

One more thing.  Even though you have different record counts, don't assume that the table with the most records has records with no matches in the other table.  If there are dupes in the table with the larger recordcount, and all the extra records have matches in the table with the lesser record count, you will NOT get any missing (unmatched) records in the table with the larger record count.

Check for records with dupes on the set of fields you are linking.  You MUST understand the state of the data you are dealing with, otherwise, you know what "ASSUME" means....

Thanks for input. I figured it out!