Results 1 to 5 of 5
  1. #1
    hack4u is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    2

    Multiple Joins - SQL Error

    I'm trying to write a query which will combine multiple ODBC database tables into 1 new table. However I keep Getting a syntax error, what am I missing?



    Code:
    SELECT DISTINCT
       a.STORE_CD
      ,SUBSTRING(a.STORE_NAME, 5, LEN(a.STORE_NAME) - 4)
      ,a.ITM_CD
      ,a.DES
      ,b.DEPT_LONG
      ,b.SUB_DEPT_LONG
      ,b.SUB_DEPT_FULL
      ,b.CLASS_LONG
      ,b.SUB_CLASS_LONG
      ,a.STATUS
      ,a.SKU_TYPE
      ,d.VE_CD
      ,d.GERS_VE_CD
      ,ISNULL(c.BUYER_CLASS, 'M')
      ,ISNULL(c.REPL_FLAG, 'N')
      ,ISNULL(c.VISUAL_PRESENTATION, 0)
      ,ISNULL(c.JOB_SIZE, 0)
      ,c.START_DT
      ,c.STOP_DT
      ,CAST(e.RET_PRC) AS [RET_PRC]
      ,a.SQFT_CONV_FACTOR
      ,CAST(e.RET_PRC / a.SQFT_CONV_FACTOR) AS [RET_PRC_SQFT]
      ,a.BOX_QTY AS [Box Qty]
      ,a.BOX_QTY * SQFT_CONV_FACTOR AS [Sqft per Box]
     
    FROM (SELECT DISTINCT STORE_CD, STORE_NAME, ITM_CD, DES, STATUS, SKU_TYPE, DEPT, SUB_DEPT, CLASS, SUB_CLASS, VE_CD, SQFT_CONV_FACTOR, MMS_STORE_TYPE, OPEN_DT, dbo.MMS_ITM.BOX_QTY FROM DM.dbo.MMS_ITM CROSS JOIN DM.dbo.MMS_STORE) AS a
     
    JOIN dbo_MMS_ITM_HIERARCHY AS b
      ON a.DEPT=b.DEPT AND a.SUB_DEPT=b.SUB_DEPT AND a.CLASS=b.CLASS AND a.SUB_CLASS=b.SUB_CLASS
     
    LEFT OUTER JOIN dbo_E3_ASR_SKU AS c
      ON a.ITM_CD = c.ITM_CD AND a.STORE_CD = c.STORE_CD
     
    INNER JOIN dbo_MMS_VE AS d
      ON a.VE_CD = d.VE_CD
     
    INNER JOIN dbo_MMS_ITM_STORE AS e
      ON a.STORE_CD = e.STORE_CD AND a.ITM_CD = e.ITM_CD
     
    INNER JOIN dbo_MMS_STORE AS f
      ON c.STORE_CD = f.STORE_CD
     
    INNER JOIN dbo_MMS_REGION AS g
      ON f.REGION = ISNULL(g.REGION_CD,0)
     
    WHERE a.MMS_STORE_TYPE IN ('S', ' ')
      AND a.STORE_CD = 101
      AND a.STATUS NOT IN ('I');
    Click image for larger version. 

Name:	Capture1.JPG 
Views:	12 
Size:	22.8 KB 
ID:	26145

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You got a lot going on there, and finding the error may prove to be not so easy without seeing all the data.

    When encountered with a situation like this, here is how I usually approach it:
    Start over, beginning with writing a query that just returns data from your main table.
    Once you get that working properly, join in one more table, and make your code edits.
    Continue that process until you get through all of them.

    By building it "piece-by-piece" like that, you should be able to zero in one which step is causing the error, and focus on that new code you just added.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is way too complex to troubleshoot simply. Start a new query and add one table at a time.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Where are you running the query - in MS Access or as a SQL-SERVER query? If it's running is Access I think there are function names Access doesn't understand, i.e. SUBSTRING, CAST.

  5. #5
    hack4u is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    2
    John_G , I think you may have a point there.

    I have another machine here run Task Scheduler everyday at 8am to open up the file and to execute the macro inside it which tells it to drop my table and to run the query and put it into a table. The problem I have been getting is that sometimes it doesn't execute properly. I also get a ODBC--Connection failure. The task scheduler runs the tasks in MS Access on a different machine and sometimes it does execute so I don't know if it needs to be run on SQL Server if it occasionally works.

    My IT department doesn't support my Access tables so I'm really scratching my head here.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Statement Error - Joins
    By boilermaker1997 in forum Queries
    Replies: 4
    Last Post: 08-10-2015, 06:59 AM
  2. multiple inner joins getting syntax error
    By ringram in forum Modules
    Replies: 5
    Last Post: 10-23-2014, 02:58 PM
  3. Multiple Inner Joins
    By comfygringo in forum Queries
    Replies: 5
    Last Post: 09-03-2013, 10:49 PM
  4. Replies: 6
    Last Post: 06-21-2013, 08:14 AM
  5. Multiple outer joins - error message
    By Lipi in forum Queries
    Replies: 1
    Last Post: 09-16-2010, 02:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums