[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Access left join query

Hello,

I have a query that was written in SQL.  I need to bring the query into Access and am having a lot of trouble with the left joins.  I'm getting a lot of errors saying missing expression, etc...  Could some one help me convert this to something Access understands?
---------------------------------
SELECT M.MIL_CODE

FROM
             QUOTE          Q

  INNER JOIN QUOTE_ITEM     QI  ON QI.QUOTE_ID        = Q.QUOTE_ID
                                    AND QI.QUOTE_VERS_STOP = Q.QUOTE_VERS

  INNER JOIN BOM_PIECE      BP  ON QI.QUOTE_ITEM_ID  = BP.QUOTE_ITEM_ID

  INNER JOIN EXTN           EX  ON BP.EXTN_LIB_ID    = EX.EXTN_LIB_ID
                                     AND BP.EXTN_ID        = EX.EXTN_ID

  INNER JOIN CLASSIFICATION CL  ON EX.CLASSIF_LIB_ID = CL.CLASSIF_LIB_ID
                               AND EX.CLASSIF_ID     = CL.CLASSIF_ID

  LEFT JOIN BOM_MIL         BM  ON BM.QUOTE_ITEM_ID  = BP.QUOTE_ITEM_ID
                                     AND BM.INSTANCE_IDENT = BP.INSTANCE_IDENT
                               AND BM.OBJ_ID         = BP.OBJ_ID

  LEFT JOIN MILLING         M   ON M.MIL_LIB_ID      = BM.MIL_LIB_ID
                                     AND M.MIL_ID          = BM.MIL_ID
---------------------------------

Thanks,
Joel
0
Genius123
Asked:
Genius123
  • 2
  • 2
1 Solution
 
PatHartmanCommented:
Access uses an arcane syntax with lots of parentheses for joins.  The simplest solution if you are not familiar with it is to copy the select part of the query and then only copy the table names part of the FROM clause.  Then in QBE view, draw the joins and let Access generate the SQL for you.
0
 
Genius123Author Commented:
I tried that.  When I draw the left joins, I get this error.  I've tried making queries of queries and am just having a hard time with it....

left-join.pdf
0
 
PatHartmanCommented:
The error message actually tells you how to solve the problem.

The way that joins work, you cannot have an inner join to the right of an outer join.

If you cannot rearrange the tables to conform to the rule, pluck out the two tables joined with a left join and put them in a separate query.  In the original query, replace the two tables with the new query.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you also have joins to dbo_BOM_MIL from 2 places. Perhaps a query with what you need from BOM_PIECE and BOM_MIL?

if relationships had referential integrity, the join line ends would be labeled with "1" or the infinity sign
0
 
Genius123Author Commented:
thanks, that worked.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now