Access Union Query to get Full Outer Join

Question

I have 2 tables TestFname & TestLname. Each have some entries with first name only, some with last name only and some with both first and last names. Each Person has unique Number, so if Jack Smith occurs as "Jack" in TestFname and "Smith" in TestLname, Jack's number is "2" in both tables.

I want query result to show all unique Persons only once, and in case of Jack Smith, combine "Jack" from TestFname with "Smith" from TestLname in one record "Jack Smith" in query result.

    SELECT IIf([TestFname.F_name] Is Null,[TestLname.F_Name],[TestFname.F_Name]) AS FFName,IIf([TestFname.Number] Is Null, [TestLname.Number],[TestFname.Number]) AS Nnumber
FROM TestFname 
LEFT JOIN TestLname 
ON TestFname.Number = TestLname.Number
UNION 
SELECT  IIf([TestFname.L_name] Is Null,[TestLname.L_Name],[TestFname.L_Name]) AS LLName,IIf([TestFname.Number] Is Null, [TestLname.Number],[TestFname.Number]) AS Nnumber
FROM TestFname 
RIGHT JOIN TestLname 
ON TestFname.Number = TestLname.Number;

This gives only 2 columns output: FFName and Nnumber (no LLname), and Jack Smith occurs in 2 records rather than in 1 record with both FFName and LLname in separate columns.

score 0 · Accepted Answer · answered Sep 08 '20 at 17:42

Need all 3 calcs in both SELECT statements.

SELECT IIf([TestFname.Number] Is Null, [TestLname.Number], [TestFname.Number]) AS Nnumber, 
   IIf([TestFname.F_name] Is Null,[TestLname.F_Name],[TestFname.F_Name]) AS FFName, 
   IIf([TestFname.L_name] Is Null,[TestLname.L_Name],[TestFname.L_Name]) AS LLName 
   FROM TestFname 
   LEFT JOIN TestLname ON TestFname.Number = TestLname.Number
UNION 
SELECT IIf([TestFname.Number] Is Null, [TestLname.Number], [TestFname.Number]), 
   IIf([TestFname.F_name] Is Null, [TestLname.F_Name], [TestFname.F_Name]), 
   IIf([TestFname.L_name] Is Null,[TestLname.L_Name],[TestFname.L_Name]) 
   FROM TestFname 
   RIGHT JOIN TestLname ON TestFname.Number = TestLname.Number;

Access Union Query to get Full Outer Join

1 Answers1