Access FULL JOIN for 7 tables

Question

How you doing?

I'm new in this SQL/Access world and I really need your wisdom, in order to do a simulated FULL JOIN in SQL-ACCESS or a best option to merge 7 tables.

I've tried with LEFT/RIGHT JOIN + UNION but didn't work out. These tables have only two values columns:

ClientsID
PriceTypes - different price types in the tables (Price1_tbl, Price2_tbl...Price7_tbl) that each could have a few common ClientsID in the 7 tables.

What I need as a result is:

To get a unique ClientsID column (of course not duplicated) appending every ClientIDs from these 7 tables;
And for the prices: the prices in the 7 tables in the 7 columns right the ClientIDs, matching if they match ClientID or with a NULL value if that table doesn't have that ID with a price.

Should be something like the print I've attached below:

I put only 3 tables for the example. I know Access doesn't support FULL JOIN, I'm open to new ways to solve this issue.

I hope to be clear in my question,

Thanks in advance!!

See this: https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access — Kostas K., Apr 05 '18 at 14:39
*I've tried with LEFT/RIGHT JOIN+UNION but didn't work out.* ... please post such efforts and any errors or undesired results. — Parfait, Apr 05 '18 at 14:54
Possible duplicate of [Simulate FULL OUTER JOIN with Access on more than two tables](https://stackoverflow.com/questions/24700881/simulate-full-outer-join-with-access-on-more-than-two-tables) — Erik A, Apr 05 '18 at 15:55
if you read the post you can see is not the same Erik. it matches different tables. I tried to match 7 tables with an unique primary key, by the way is already done. ;) — Rokrman22, Apr 28 '18 at 02:08

Parfait · Accepted Answer · 2018-04-05T16:14:02.493

Since FULL OUTER JOIN is not available in MS Access and requires a UNION ALL of LEFT JOIN and RIGHT JOIN, consider breaking apart the process into several queries. This avoids very nested subquery derived tables:

Query 1 (first two tables)

SELECT a.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a LEFT JOIN ClientPriceB b
  ON a.CLIENTID = b.CLIENTID 
WHERE a.ID IS NOT NULL;

UNION ALL 

SELECT b.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a RIGHT JOIN ClientPriceB b
  ON a.CLIENTID = b.CLIENTID 
WHERE b.ID IS NOT NULL;

Query 2 (first two tables query + third table) -- GENERATES POSTED DESIRED OUTPUT

SELECT m.*
FROM 
  (SELECT f.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
   FROM Query1 f
   LEFT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
   WHERE f.ClientID IS NOT NULL

   UNION ALL

   SELECT c.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
   FROM Query1 f
   RIGHT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
   WHERE c.ClientID IS NOT NULL
  )  AS m
ORDER BY m.ClientID;

Then repeat process for all others:

Query 3 (first three tables query + fourth table)

...

Query 4 (first four tables query + fifth table)

...

Query 5 (first five tables query + sixth table)

...

Query 6 (first six tables query + seventh table) --FINAL RESULT

...

NOTE: You may run into an MS Access UNION limit or query too complex and so consider outputting queries into temp tables with make-table calls: SELECT * INTO FROM (<query>).

Access FULL JOIN for 7 tables

1 Answers1