-1

The query works pretty fine, I can view result as a table and sql code on his own without any problem. However then I try to press query design button, it crashed without saying why. enter image description here

My code is pretty simple, after I added one more left join out of A subquery such error started to appear. This way last left join definitely cause the problem. Tried to join without using subquery but I get problem saying about ambiguous outer join. I'm newbie with access but I heard about several bugs in that program, any suggestion how to fix?

This problem query:

    select    A.*,targetresp.* 
    from      (
    SELECT  * 
    FROM   target INNER JOIN ((source INNER JOIN InstanceList 
                    ON   source.INFO_SYSTEM_TYPE_CD = InstanceList.INFO_SYSTEM_TYPE_CD) 
INNER JOIN (N_table_transform INNER JOIN S2T 
                    ON    N_table_transform.N_table = S2T.N_table) 
                    ON    source.ID = S2T.source_id) 
                    ON    target.id = S2T.target_id ) as A

    left join targetresp 
                    on          a.target_TableName = targetresp.tablename;
Rocketq
  • 5,423
  • 23
  • 75
  • 126
  • 1
    when you ask a question try to simplify the issue. For example all those fields doesnt add to the issue, a `select *` is just enough. Now the best think you can do is add one inner join at a time and try to see where is the problem. – Juan Carlos Oropeza Aug 13 '15 at 12:45

1 Answers1

1
SELECT InstanceList.*
FROM 
    N_table_transform 
    INNER JOIN (((S2T INNER JOIN target 
                  ON S2T.target_id = target.Id) 
    LEFT JOIN targetresp ON target.target_TableName = targetresp.target_TableName) 
    INNER JOIN (InstanceList 
    INNER JOIN source ON InstanceList.INFO_SYSTEM_TYPE_CD = source.INFO_SYSTEM_TYPE_CD) 
    ON S2T.source_id = source.Id) 
    ON N_table_transform.N_table = S2T.N_table;

I just use the access 2013 designer and build the query.

enter image description here

On the bottom switch between SQL and DESIGNER

enter image description here

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 1
    Yea, you will be right If I use normal DBMS, but this boring Access, which requires parentheses in the `FROM` clause for queries which include more than one join, look http://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access – Rocketq Aug 13 '15 at 13:09
  • As your question is regarding the last `left join` try do that first and go backwards instead. Also your link show you can use designer to add the join and this will add the parenthesis for you – Juan Carlos Oropeza Aug 13 '15 at 13:12
  • Can you check my update answer and let me know? I just create your schema db the best I could (hope is ok) and then use designer to build the query. Hope access 2013 is similar to 2010 – Juan Carlos Oropeza Aug 14 '15 at 17:17