1

I have multiple (3+) Excel tables (only first row is given for presentation purpose for first 3 tables). What I am trying to achive is to show all rows from all the tables, and join them where a common value exists, i.e. I am trying to simulate FULL OUTER JOIN.

TBL1

IDno         IDname          d10         d30        datecreated  

12-778        Robetrt         62         72          9/25/2020

TBL2

IDno         IDname          result3      datecreated    

12-124        Sam2            text22        9/25/2018

TBL3

IDno         IDname          Area         datecreated    

12-324        Trinton         6.25         9/25/2013

Sometimes IDno between diferent tables are the same but in the majority of cases they are different.

I made a UNION query to return all the IDno from the tables:

SELECT TBL1.IDno, TBL1.IDname FROM TBL1 UNION SELECT TBL2.IDno, TBL2.IDname FROM TBL2 UNION SELECT TBL3.IDno, TBL3.IDname FROM TBL3;

I named the query IDs. Then, I would like to get a table which would show all the rows and also combine the rows where the IDno is the same:

RESULT

IDno     IDname     d10       d30       result3      Area

I used:

SELECT
IDs.IDno, IDs.IDname,
TBL1.d10, TBL1.d30,
TBL2.result3,
TBL3.Area
FROM ((IDs
left join TBL1 on IDs.IDno = TBL1.IDno)
left join TBL2 on TBL1.IDno = TBL2.IDno)
left join TBL3 on TBL1.IDno = TBL3.IDno

UNION

SELECT
IDs.IDno, IDs.IDname,
TBL1.d10, TBL1.d30,
TBL2.result3,
TBL3.Area
FROM ((IDs
right join TBL1 on IDs.IDno = TBL1.IDno)
right join TBL2 on TBL1.IDno = TBL2.IDno)
right join TBL3 on TBL1.IDno = TBL3.IDno

WHERE 
IDs.IDno IS NULL;

The end result is LEFT JOINed tables with some additional rows that show IDno and IDname but no data from TBL2 or TBL3. I cannot figure out how to UNION left and right JOIN to achive FULL OUTER JOIN in this case. It works with 2 tables but not three or more in Acess. Is there a way to join multiple tables in Excel (Power query) or elsewhere without the need for SQL server?

Bennet Turner
  • 27
  • 5
  • I assume you have read https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access ? -- It would help to add a few records of sample data, and expected vs. actual result, showing the problem. https://ozh.github.io/ascii-tables/ is useful for sample data. – Andre Feb 01 '21 at 12:14
  • I was a bit naive with my question. Accordin to [link](https://stackoverflow.com/questions/24700881/simulate-full-outer-join-with-access-on-more-than-two-tables) it is possible to make it work with 3 tables but anything beyond that does not work. So maybe better question, is there any way to outer join multiple (4 or more) tables without mysql server using MS Excel or its extenstions? – Bennet Turner Feb 01 '21 at 12:19
  • Good find! This Q is probably a duplicate of that one. Looking for tools etc. is off-topic here. – Andre Feb 01 '21 at 14:10
  • Does this answer your question? [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) – June7 Feb 01 '21 at 19:45

0 Answers0