Finding unmatched records between 2 tables in MS Access

msibley
msibley used Ask the Experts™
on
I have a database in MS Access 365 containing a data on subjects, with over 300,000 rows. There are several rows for each subject. Columns for a subject are comparable for name (short text with 5 letter code), date (number with yyyymmdd), Condition (short text with "EO" or "EC"), TypeFZ (short text with "ABSF" or "ABSZ"), AgeRange(number), and Site (short text with 5 characters). There should be the same number of rows for each of the 2 TypeFZ fields. When I run queries to create 2 tables, 1 for each TypeFZ, there aren't the same number of rows in each table. One table has 38 less records than the others.

I've tried using the Find Unmatched Query Wizard, but I need to select on all of the above columns. I've tried various types of joins and syntax, but either get error messages or unusable results.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Just use left join from the table with the bigger recordcount ...the null ones should be the unmatched
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

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

Author

Commented:
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)
Distinguished Expert 2017

Commented:
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
Mark EdwardsChief Technology Officer

Commented:
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.
Mark EdwardsChief Technology Officer

Commented:
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....
Mark EdwardsChief Technology Officer

Commented:
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....
Software & Systems Engineer
Commented:
I will take a wild guess and work like this
SELECT A.Client
	,A.DATE
	,A.Cond
	,A.TypeFZ
	,A.AgeRange
	,A.Site
        ,B.Client
FROM LEABSF AS A
LEFT JOIN LEABSZ AS B ON A.[Client] = B.[Client]

Open in new window

If you don't get any Null results on the LEABSZ.Client "reverse it"
SELECT A.Client
	,A.DATE
	,A.Cond
	,A.TypeFZ
	,A.AgeRange
	,A.Site
        ,B.Client
FROM LEABSZ AS A
LEFT JOIN LEABSF AS B ON A.[Client] = B.[Client]

Open in new window

Now you should get Null Records on the LEABSF.Client

Author

Commented:
Thanks for input. I figured it out!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial