0

I am trying to join three tables together in microsoft access, allowing for null entries with Table 1 being my primary table so all the records show.

I have this code that works linking only 2 tables. 

SELECT [1].IDCode, [2].GiftDate, [2].FundId__1, [2].fund_name 
FROM 1 LEFT JOIN 2 ON [1].IDCode = [2].IDCode 
WHERE ((([1].IDCode) Is Not Null));

But I want to add a third. I have this, but each time I try to run it, I get an error (Invalid Operation).

SELECT [1].IDCode, [2].GiftDate, [2].FundId__1, [2].fund_name, [3].Rep_Type
FROM (1 LEFT JOIN 2 ON [1].IDCode = [2].IDCode) LEFT JOIN 3 ON [1].IDCode = [3].IDCode
WHERE ((([1].IDCode) Is Not Null));

My code is now this and I'm still receiving the invalid operation.

SELECT [A].IDCode, [A].GiftDate, [A].FundId__1, [A].fund_name, [3].Rep_Type
FROM 
(SELECT [1].IDCode, [2].GiftDate, [2].FundId__1, [2].fund_name
FROM 1
LEFT JOIN 2
ON [1].IDCode = [2].IDCode) A
LEFT JOIN 3
ON [A].IDCode = [3].IDCode
WHERE ((([1].IDCode) Is Not Null));
blink444
  • 1
  • 2
  • The answer to this question: http://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables may help you – Syntax Error Nov 28 '16 at 21:10
  • Will 3's always exist in 2s? if so join though 2. If not () order will matter and make this more of a challenge. – xQbert Nov 28 '16 at 21:12
  • 3 will not always exist in 2. – blink444 Nov 28 '16 at 21:38
  • I cannot recreate issue. Your second query runs fine in Access 2013. Maybe post your table schema as my example might not align with yours. – Parfait Nov 28 '16 at 23:32

1 Answers1

0

If 1-->2-->3 then the third on join should be 2.idcode = 3.idcode

SELECT [1].IDCode, [2].GiftDate, [2].FundId__1, [2].fund_name, [3].Rep_Type
FROM (1 LEFT JOIN 2 ON [1].IDCode = [2].IDCode) LEFT JOIN 3 ON [2].IDCode = [3].IDCode
WHERE ((([1].IDCode) Is Not Null));

However if the joins is really back to 1...

Since Access is picky about the ()'s and joining back to 2.

So I don't get lost with the ()'s I just created an inline view to manage the joins.

SELECT [A].IDCode, [A].GiftDate, [A].FundId__1, [A].fund_name, [3].Rep_Type
FROM
(SELECT [1].IDCode, [2].GiftDate, [2].FundId__1, [2].fund_name
 FROM 1 
 LEFT JOIN 2 
   ON [1].IDCode = [2].IDCode) A
LEFT JOIN 3 
  ON [A].IDCode = [3].IDCode
WHERE [A].IDCode Is Not Null;
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 1-> 2 and 1-> 3 I tried the bottom code subbing the A instead of 1/2, and I am still getting an Invalid Operation. – blink444 Nov 28 '16 at 21:40
  • Should there be two or 1 underscore for FundID_1? you seem to have 2 which is odd. Alternative would be to create the A as a view and select from the view perhaps access for some reason doesn't like the inline view. – xQbert Nov 28 '16 at 21:46