Joining two tables that represent changes over time in Access

Question

I am using Access and i am trying to join two tables that have the following structure:

Table 1:

Col1   Col2   T1
C      A      1.1
B      A      1.2
D      A      1.3

Table 2:

Col1   Col2   T2
C      A      2.1
B      A      2.2
E      A      2.3

And i want the resulting table to be as the following table:

Col1   Col2   T1    T2
C      A      1.1   2.1
B      A      1.2   2.2
D      A      1.3   0
E      A      0     2.3

I tried so many solutions from the internet (outer join), but i am not getting the required results! I would really appreciate if someone could help me with this issue!!!

coalesce-alternative-in-access https://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql — Keith John Hutchison, Jul 24 '17 at 03:19

score 1 · Answer 1 · answered Jul 24 '17 at 09:47

Unfortunately Access doesn't support a FULL OUTER join. You need to perform two joins and then union the tables.

This will give your table in no particular order. The NZ(T1,0) replaces T1 with a 0 if the field is NULL.

SELECT    Table1.Col1
        , Table1.Col2
        , NZ(T1,0)
        , NZ(T2,0)
FROM    Table1 LEFT JOIN Table2 ON Table1.Col1 = Table2.Col1 AND Table1.Col2 = Table2.Col2

UNION SELECT    Table2.Col1
        , Table2.Col2
        , NZ(T1,0)
        , NZ(T2,0)
FROM    Table2 LEFT JOIN Table1 ON Table2.Col1 = Table1.Col1 AND Table2.Col2 = Table1.Col2

To get the exact field names you want in your final query you'll have to wrap the query in another query - you can't use call NZ(T1,0) AS T1 as you'll get a circular reference warning.

SELECT  Col1, Col2, T1Col AS T1, T2Col AS T2
FROM        (
            SELECT    Table1.Col1
                    , Table1.Col2
                    , NZ(T1,0) AS T1Col
                    , NZ(T2,0) AS T2Col
            FROM    Table1 LEFT JOIN Table2 ON Table1.Col1 = Table2.Col1 AND Table1.Col2 = Table2.Col2

            UNION SELECT    Table2.Col1
                    , Table2.Col2
                    , NZ(T1,0)
                    , NZ(T2,0)
            FROM    Table2 LEFT JOIN Table1 ON Table2.Col1 = Table1.Col1 AND Table2.Col2 = Table1.Col2
            )

I've no idea how to get the sort order you're after.

score 0 · Answer 2 · answered Jul 24 '17 at 03:20

0

SELECT COALESCE(A.Col1,B.Col1 ) AS Col1,
       COALESCE(A.Col2,B.Col2 ) AS Col2,
       COALESCE(A.T1,0) AS T1,
       COALESCE(B.T2,0) AS T2
  FROM TABLE1 A
FULL OUTER JOIN
       TABLE2 B
    ON A.Col1 = B.Col1;

answered Jul 24 '17 at 03:20

Teja

13,214
36
93
155

Thank you so much, but i just tried this now in Access and i got got Syntax error in FROM clause, and the keyword FULL is highlighted? – Max Marsh Jul 24 '17 at 03:47
https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access – Andre Jul 24 '17 at 09:45

Joining two tables that represent changes over time in Access

2 Answers2