When field "IS NULL" join on an alternative field - Access 2007

Question

So the short end of it is this is what I want to do, but I don't know the proper syntax.

Table 1, Table 2 Name, SSN, DOB, List, Date

I want to do a left join using the SSN, but when the SSN field IS NULL I want it to join on the DOB field Where the Name matches.

I can't join on name due to the file being 19k records and most of them are common names.

Answer 1

I believe that you can do what you want with a simple LEFT JOIN and the correct JOIN conditions. The trick in Access is that IT DOES NOT LIKE left joins with too few parentheses. When in doubt, add more parentheses. You will be surprised at what it makes possible in Access.

SELECT
   T1.*,
   T2.*
FROM
   (Table1 AS T1
   LEFT JOIN Table2 AS T2 ON (
      (T1.SSN = T2.SSN)
      OR (
         (T1.SSN IS NULL)
         AND (T1.DOB = T2.DOB)
         AND (T1.Name = T2.Name)
      )
   ));

If this doesn't work, please let me know and I'll see what I can do.

Note that you may or may not be able to go to the design view of this in the GUI query editor.

Also note that since NULL does not equal NULL, you don't need additional conditions before the OR about X.SSN IS NOT NULL.