CODEWITHSUNDEEP
ms-accessunionfull-outer-join
BigDave_76
BigDave_76

Reputation: 49

MS Access Full Outer Join using 2 fields?

I have 2 tables that I want to do a FULL OUTER JOIN on in MS Access.

Say I have Table A which looks like this: 

A.ID    |  A.Value  | A.DATE
--------+-----------+----------
  1     |     30    | 05/2018
  1     |     28    | 06/2018
  1     |     26    | 07/2018
  2     |     250   | 04/2018
  2     |     252   | 05/2018
  2     |     240   | 06/2018

And Table B which looks like this:

B.ID    |  B.FCST   | B.OUTDATE
--------+-----------+-----------
  1     |     35    | 06/2018
  1     |     33    | 07/2018
  1     |     30    | 08/2018
  2     |     300   | 06/2018
  2     |     280   | 07/2018
  2     |     260   | 08/2018

And I need to perform Joins and Unions to achieve this:

 A.ID   |  A.Value  | A.DATE  |  B.FCST   | B.OUTDATE
--------+-----------+---------+-----------+------------
  1     |     30    | 05/2018 |     -     |     -
  1     |     28    | 06/2018 |     35    |   06/2018
  1     |     26    | 07/2018 |     33    |   07/2018
  1     |     -     |    -    |     30    |   08/2018
  2     |     250   | 04/2018 |     -     |     -
  2     |     252   | 05/2018 |     -     |     -
  2     |     240   | 06/2018 |     300   |   06/2018
  2     |     -     |    -    |     280   |   07/2018
  2     |     -     |    -    |     260   |   08/2018

So I need to do an Inner Join with A.ID = B.ID, AND A.DATE = B.OUTDATE, and then somehow get "earlier" data from Table.A to "Sit on top" of the inner joined data, and the "later" data from Table.B to do the opposite. This is my attempt so far:

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A JOIN Table.B ON A.ID = B.ID AND A.DATE = B.OUTDATE

UNION ALL

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A LEFT JOIN Table.B ON A.ID = B.ID;
WHERE B.ID IS NULL

UNION ALL

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A RIGHT JOIN Table.B ON A.ID = B.ID
WHERE A.ID IS NULL

ORDER BY A.ID ASC;

But I've missed the mark it appears. I'm getting duplicate lines, and it just looks like an Inner Join. I will gladly take any advice as to help get this right.

Upvotes: 1

Views: 458

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

I would suggest a union of two left joins to give the same result as full outer, and finally with a touch of sorting to yield the desired ordering:

select c.* from
(
    select a.id, a.value, a.date, b.fcst, b.outdate
    from a left join b on a.id = b.id and a.date = b.outdate
    union
    select b.id, a.value, a.date, b.fcst, b.outdate
    from b left join a on a.id = b.id and a.date = b.outdate
) c
order by c.id, nz(c.date, c.outdate)

Upvotes: 2

Related Questions