Why does my left join in Access have fewer rows than the left table?

Question

I have two tables in an MS Access 2010 database: TBLIndividuals and TblIndividualsUpdates. They have a lot of the same data, but the primary key may not be the same for a given person's record in both tables. So I'm doing a join between the two tables on names and birthdates to see which records correspond. I'm using a left join so that I also get rows for the people who are in TblIndividualsUpdates but not in TBLIndividuals. That way I know which records need to be added to TBLIndividuals to get it up to date.

SELECT TblIndividuals.PersonID AS OldID, 
TblIndividualsUpdates.PersonID AS UpdateID
FROM TblIndividualsUpdates LEFT JOIN TblIndividuals 
ON ( (TblIndividuals.FirstName = TblIndividualsUpdates.FirstName) 
and (TblIndividuals.LastName = TblIndividualsUpdates.LastName) 
AND (TblIndividuals.DateBorn = TblIndividualsUpdates.DateBorn 
    or (TblIndividuals.DateBorn is null 
        and (TblIndividuals.MidName is null and TblIndividualsUpdates.MidName is null 
            or TblIndividuals.MidName = TblIndividualsUpdates.MidName))));

TblIndividualsUpdates has 4149 rows, but the query returns only 4103 rows. There are about 50 new records in TblIndividualsUpdates, but only 4 rows in the query result where OldID is null.

If I export the data from Access to PostgreSQL and run the same query there, I get all 4149 rows.

Is this a bug in Access? Is there a difference between Access's left join semantics and PostgreSQL's? Is my database corrupted (Compact and Repair doesn't help)?

Answer 1

ON ( 

        TblIndividuals.FirstName = TblIndividualsUpdates.FirstName

        and 

        TblIndividuals.LastName = TblIndividualsUpdates.LastName

        AND (
                 TblIndividuals.DateBorn = TblIndividualsUpdates.DateBorn      
                 or 
                 (
                     TblIndividuals.DateBorn is null          
                     and 
                     (
                     TblIndividuals.MidName is null 
                     and TblIndividualsUpdates.MidName is null              
                     or TblIndividuals.MidName = TblIndividualsUpdates.MidName
                     )
                 )
             )
    );

What I would do is systematically remove all the join conditions except the first two until you find the records drop off. Then you will know where your problem is.