Access 2019: How to join two tables from left and right?

[jamal numan]

Access 2019: How to join two tables from left and right?




I couldn’t figure out to join two tables from left and right.

In the screenshot below, I wanted to join the two tables (T1 and T2) to produce T3. All the available options for the join don’t perform joining tables from left and right.

The accdb file is zipped and attached

[Micron]

You will have to do it in steps.
qry1, join T1 to T2 using one of either option 2 or 3. Say, all of T1.
qry2, join T2 to qry1 using the other option - all of T2
Which one ends up being either the 2nd or 3rd option just depends on which is on the left vs right side and which way the joins go, so you might have to experiment.

[jamal numan]

You will have to do it in steps.
qry1, join T1 to T2 using one of either option 2 or 3. Say, all of T1.
qry2, join T2 to qry1 using the other option - all of T2
Which one ends up being either the 2nd or 3rd option just depends on which is on the left vs right side and which way the joins go, so you might have to experiment.

But how to get the result that I’m looking for as shown in T3? These two quires don’t produce a full join from left and right

[Micron]

Have been dealing with a death in the family so have not been able to think about this much. If there is a query solution I'm not seeing one. I considered Cartesian query, left and right joins as mentioned, Union queries and a subquery. I don't think it's possible because there is no field that provides a common connection between the tables.

However, I do think you can achieve the result shown by using vba code. One way would involve creating 2 recordsets and doing a nested loop and writing the field values to a table. By that I mean
- in the outer loop, get the 2 field values from rs1 (records from T1)
- in the inner loop, get the 2 field values from rs2 (records from T2)
- write the 4 field values to the table
- repeat

Special consideration would required since one recordset can have a different record count than the other. You show 2 sets with the same record count but one has a higher max value (9 versus 7) which would require a different approach than normal because the output record count is actually the higher number, so there's that too. There might be other options such as collections, dictionary objects (which I have never used) and Heaven forbid, perhaps a 4 dimensional array which I won't even attempt.

So my suggestion would be to attempt to arrive at the desired outcome by using recordsets in vba. If this is a one time operation I'd say use Excel, drag to create your ranges, link to your sheet as you would a table in Access and import your sheet into your table or just use the sheet. IMO it's not worth the effort to write a procedure unless it will be a repeating exercise. Even then, the sheet solution might be easier for you.

[June7]

Build the two queries then build a third that is UNION of the two.

Did you ask this same question on StackOverflow https://stackoverflow.com/questions/...47428#69847428?

[mike60smart]

Hi

If you had created the design of your tables correctly using Autonumbers for Each Table vice Text Data Types
you would be able to generate a query to produce the desired output.

In the attached revised database I added Autonumbers to each Table and then Linked T1 to T2 using IDT1

[ArviLaanemets]

UNION 3 subqueries, like:

SELECT t1.ID_T1, t2.ID_T2, t1.F1, t2.V2 FROM T1 t1 INNER JOIN T2 t2
UNION
SELECT t1.ID_T1, Null AS ID_T2., t1.F1, Null AS V2 FROM T1 t1 WHERE t1.ID_T1 NOT IN (SELECT ID_T2 FROM T2)
UNION
SELECTNull AS ID_T1, t2.ID_T2, Null AS F1, t2.V2 FROM T2 t2 WHERE t2.ID_T1 NOT IN (SELECT ID_T1 FROM T1)

[jamal numan]

I got help from a colleague! The code is quite simple to join from left and right

SELECT T1.ID1, T1.A, T2.ID2, T2.B
FROM T1 LEFT JOIN T2 ON T1.ID1 = T2.ID2;
UNION
SELECT T1.ID1, T1.A, T2.ID2, T2.B
FROM T1 Right JOIN T2 ON T1.ID1 = T2.ID2;


the data is attached

[jamal numan]

Similarly, 3 tables can be joined from left and right with two queries:

In Q1:

SELECT T1.ID1, T1.A, T2.ID2, T2.B
FROM T1 LEFT JOIN T2 ON T1.ID1 = T2.ID2;
UNION
SELECT T1.ID1, T1.A, T2.ID2, T2.B
FROM T1 right JOIN T2 ON T1.ID1 = T2.ID2;

In Q2:
SELECT Q1.ID1, Q1.A, Q1.ID2, Q1.B, T3.ID3, T3.C
FROM Q1 LEFT JOIN T3 ON Q1.ID1 = T3.ID3;
UNION
SELECT Q1.ID1, Q1.A, Q1.ID2, Q1.B, T3.ID3, T3.C
FROM Q1 right JOIN T3 ON Q1.ID1 = T3.ID3;

[jamal numan]

There is no need to write these syntax as they can be drag and drop (just add the word union and replace left by right as shown in the screenshot)

[mike60smart]

Can you explain why you need to generate a table based on tables of unrelated data ?
Or have I missed something here?

[jamal numan]

Can you explain why you need to generate a table based on tables of unrelated data ?
Or have I missed something here?

Please, have a look in three tables above. They do have common data but still they do have data that are not available in the others!