How to merge recordsets with different fields

In SQL, union can be used to append the rows of a recordset (table or query) to another one with the same fields. You can also append recordsets with different fields by combining the fields of both and assigning null values to fields for rows in a recordset that does not have those fields.

But what if each recordset has values that correspond to a key in the other one? Then combining fields with all null values for either recordset does not give the correct result.

I need to merge two recordsets with different fields in a single query. My recordsets are queries, but for the purposes of showing the problem, I’m representing them here as tables. Here is the setup in Access:

What I need is a datasheet with values for Data_1 and Data_2 when they exist, based on the values of ID1 and ID2:

ID1	ID2	Data_1	Data_2
1	1	A	X
2	1	B
3	1		Y
4	1	C	Z1
4	2		Z2

I’m trying to do this by doing a left join from ID1 to ID1_1 and to ID1_2. This gives me the correct output for ID1 values of 1, 2, and 3. But I have not been able to get the right output of the two rows for ID1 = 4.

For example, the SQL:

SELECT ID1, nz(ID2_1, ID2_2) AS ID2, Data_1, Data_2
FROM (ID1 LEFT JOIN Table_1 ON ID1.ID1 = Table_1.ID1_1) 
          LEFT JOIN Table_2 ON ID1.ID1 = Table_2.ID1_2
WHERE Data_1 Is Not Null OR Data_2 Is Not Null;

gives me two rows for ID1 = 4, but the second one is wrong:

and the SQL:

SELECT ID1, nz(ID2_1, ID2_2) AS ID2, Data_1, Data_2
FROM (ID1 LEFT JOIN Table_1 ON ID1.ID1 = Table_1.ID1_1) 
          LEFT JOIN Table_2 ON ID1.ID1 = Table_2.ID1_2
WHERE ID2_1 is not null and ID2_2 is null or
      ID2_1 is null and ID2_2 is not null or
      ID2_1 = ID2_2;

gives me only one row for ID1 = 4:

What am I doing wrong? What do I need to do to get the required output?

Answer

Consider:

SELECT Table_2.ID1_2, Table_2.ID2_2, Table_1.Data_1, Table_2.Data_2
FROM Table_2 LEFT JOIN Table_1 ON (Table_2.ID2_2 = Table_1.ID2_1) AND (Table_2.ID1_2 = Table_1.ID1_1)
UNION
SELECT Table_1.ID1_1, Table_1.ID2_1, Table_1.Data_1, Table_2.Data_2
FROM Table_1 LEFT JOIN Table_2 ON (Table_1.ID2_1 = Table_2.ID2_2) AND (Table_1.ID1_1 = Table_2.ID1_2);

=====================

Edit by questioner: This answer works perfectly, and it’s one of those things that is obvious once you get it, but did not occur to me at all when I was trying to figure this out. So I want to explain how it works.

First, the table ID1, which I had left join‘d to get all the rows of both Table_n tables included, is not needed.

Each of the two queries in the union does a left join from one Table_n to the other. This includes all the rows of the left-side table and inserts a new Data_m column, with corresponding values, where they exist, from that column of the other table. That creates a pair of tables that can be union‘d, with duplicate rows excluded, to produce the desired output.