How to apply “Full Uuter Join” in Access in order to combine multiple tables based on an ID that is

[jamal numan]

How to apply “Full Uuter Join” in Access in order to combine multiple tables based on an ID that is not available in all of them?

I wanted to join the tables T1, T2, T3 while the expected result is T4.
Which inquiry can perform this?


The data is here:
http://www.mediafire.com/file/erfipr...rJoin.mdb/file

In SQL server, this can be accomplished with the code below:

SELECT
COALESCE(T1.F1, T2.S1, T3.H1) AS R1,
T1.F2 AS R2,
T2.S2 AS R3,
T3.H2 AS R4
FROM T1
FULL OUTER JOIN T2 ON T2.S1 = T1.F1
FULL OUTER JOIN T3 ON T3.H1 = T1.F1

Thank you

Jamal

[isladogs]

Access doesn't support full outer joins.
Either use a UNION query or run the query in SQL Server and save as a view which you can then link to your Access db.
Note that both of these will be read only.
If you need your data to be editable then you will need to assign a primary key to the view or create a temp table for later synchronisation.

An easier solution would be to merge the tables in SQL Server

[jamal numan]

Access doesn't support full outer joins.
Either use a UNION query or run the query in SQL Server and save as a view which you can then link to your Access db.
Note that both of these will be read only.
If you need your data to be editable then you will need to assign a primary key to the view or create a temp table for later synchronisation.

An easier solution would be to merge the tables in SQL Server

Many thanks isladogs for the help.

How can I use the union query in Access to end up with the expected result?

Could you please help my with the code?

[June7]

A UNION query alone won't accomplish this. Couple ways to do this. Try this approach.

First query is the UNION:

SELECT F1 AS R1, F2 AS D1, Null AS D2, Null AS D3 FROM T1
UNION SELECT S1, Null, S2, Null FROM T2
UNION SELECT H1, Null, Null, H2 FROM T3;

Use that query in an aggregate query.

SELECT R1, Max(D1) AS R2, Max(D2) AS R3, Max(D3) AS R4 FROM Query1 GROUP BY R1;

Another approach:

Again, first query is UNION:

SELECT F1 AS R1 FROM T1
UNION SELECT S1 FROM T2
UNION SELECT H1 FROM T3;

Now build another query that JOINS all 3 tables to the UNION query.

[CJ_London]

a slightly different approach, still using a union query

qryR1 - get the distinct ID's

SELECT F1 AS R1 FROM T1
UNION SELECT S1 FROM T2
UNION SELECT H1 FROM T3

Now you can left join to the three tables - note access uses bracketing in the FROM clause

SELECT R1, F2 AS R2, S2 AS R3, H2 AS R4
FROM ((qryR1 R LEFT JOIN T1 ON R.R1=T1.F2) LEFT JOIN T2 ON R.R1=T2.S2)  LEFT JOIN T3 ON R.R1=T3.H2

edit: just realised this is a fuller answer to June's second suggestion - sorry June!

[June7]

Is okay, but you got the first line of SELECT a little mixed up - the field alias in wrong place.

[CJ_London]

getting too old for this! have corrected the code

[isladogs]

Hi Jamal
Sorry about the delay in replying but June and Ajax have given the details of what my response was intended to mean.
However, I would still consider doing it in SQL Server and then create a view to use in Access

[jamal numan]

a slightly different approach, still using a union query

qryR1 - get the distinct ID's

SELECT F1 AS R1 FROM T1
UNION SELECT S1 FROM T2
UNION SELECT H1 FROM T3

Now you can left join to the three tables - note access uses bracketing in the FROM clause

SELECT R1, F2 AS R2, S2 AS R3, H2 AS R4
FROM ((qryR1 R LEFT JOIN T1 ON R.R1=T1.F2) LEFT JOIN T2 ON R.R1=T2.S2)  LEFT JOIN T3 ON R.R1=T3.H2

edit: just realised this is a fuller answer to June's second suggestion - sorry June!

Many thanks guys for the help.
I tried ajax code but ended up with the following:

[CJ_London]

my bad - too many similar names, joining to the wrong fields

SELECT R1, F2 AS R2, S2 AS R3, H2 AS R4
FROM ((qryR1 R LEFT JOIN T1 ON R.R1=T1.F1) LEFT JOIN T2 ON R.R1=T2.S1)  LEFT JOIN T3 ON R.R1=T3.H1

[jamal numan]

my bad - too many similar names, joining to the wrong fields

SELECT R1, F2 AS R2, S2 AS R3, H2 AS R4
FROM ((qryR1 R LEFT JOIN T1 ON R.R1=T1.F1) LEFT JOIN T2 ON R.R1=T2.S1)  LEFT JOIN T3 ON R.R1=T3.H1

Many thanks for the prompt help Ajax. It works fine with me
Best
Jamal