1

Question: Is there another way for emulating a full outer join in MS Access - NOT using UNION or UNION ALL?

Summary: A Full outer join is emulated in MS Access using UNION. However, two queries both using the same table only different filters to form a set of results cannot be joined using UNION as it will simply place the query results back into the single column from which they were queried.

Context: The data consists of responses from 100 organisations spanning seven years. Different organisations each year, different responses per organisation per year, 75% text responses.

Data structure: Organisation Table; Question Table; Response Table; Link Table to link organisations, questions, responses via ID. (Built in SQL Server)

Purpose: Query a set of responses related to one question and compare the set to another set of responses related to another question. (Number of rows in resulting queries differs from query to query, and queries must be joined without losing data on the left side or the right side)

Building the front-end in MS Access.

The coding in SQL Server:

SELECT M.ORG_DESC, M.YEAR, M.RESP_DESC, N.ORG_DESC, N.YEAR, N.RESP_DESC
FROM
(SELECT C.RESP_RANK, C.ORG_DESC, A.YEAR, B.RESP_DESC
FROM LINK A
INNER JOIN RESPONSE B
ON A.RESP_ID = B.RESP_ID
INNER JOIN ORG_LOG C
ON A.ORG_ID = C.ORG_ID
WHERE A.QUESTION_ID = 10840) M
FULL OUTER JOIN
(SELECT C.RESP_RANK, C.ORG_DESC, A.YEAR, B.RESP_DESC
FROM LINK A
INNER JOIN RESPONSE B
ON A.RESP_ID = B.RESP_ID
INNER JOIN ORG_LOG C
ON A.ORG_ID = C.ORG_ID
WHERE A.QUESTION_ID = 10850) N
ON M.RESP_RANK = N.RESP_RANK

Result wished for:

Query1_ORG_DESC  Query1_YEAR   Query1_RESP_DESC   Query2_ORG_DESC   Query2_YEAR   Query2_RESP_DESC
--------------   -----------   ----------------   ---------------   -----------   ----------------
      A            2010               Q                  A               2010        R
    NULL           NULL             NULL                 A               2011        S
      B            2010               T                 NULL             NULL       NULL

Results when using UNION:

Query1_ORG_DESC  Query1_YEAR   Query1_RESP_DESC
--------------   -----------   ----------------
      A             2010              Q               /*result from Query1*/
      A             2010              R               /*result from Query2*/
      A             2011              S               /*result from Query2*/
      B             2010              T               /*result from Query1*/
Miki
  • 21
  • 3
  • Does this [link](http://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) on StackOverflow give you any insights? – TT. Jul 22 '15 at 10:31
  • TT - I have looked at that post; as I describe in my summary, the two sub sets of data are queried from exactly the same tables and exactly the same columns only the filter differs. So when I want to place the two sub sets of data next to each other (each of different total row number) while preserving data from both queries, the UNION gives me three columns ORG-DESC, YEAR, RESPONSE, instead of six columns ORG_DESC(1), YEAR(1), RESPONSE(1), ORG_DESC(2), YEAR(2), RESPONSE(2) – Miki Jul 22 '15 at 13:27
  • First off, if you want to give me an alert type @tt so I know when you comment on my comment (see "What happens when I comment" [here](https://stackoverflow.com/help/privileges/comment) and StackExchange thread on comments [here](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work). – TT. Jul 22 '15 at 15:25
  • @TT - Thank you for commenting on my post; it was the correct solution I was just muddled in all the detail and confused with the order of steps. – Miki Jul 23 '15 at 07:58

1 Answers1

1

My apologies

The solution is indeed a UNION.

My mistake was using UNION directly with the two queries. The correct method is creating the entire left outer query with the two queries, then creating the entire right outer query with the two queries, and finally using the UNION (limiting the right query to the data where the left query is null).

This link explains it adequately

https://support.office.com/en-ca/article/Join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6

Miki
  • 21
  • 3