Results 1 to 2 of 2
  1. #1
    rwhite7 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2014
    Posts
    1

    sql subquery alias join from clause error

    SELECT
    T1.SITE_ID
    ,T1.COMPNY
    ,T1.CUSTOMER_NO
    ,T1.LAT
    ,T1.[LONG]
    ,R1.ZIP_GROUP
    ,R1.LAT_AVG
    FROM (
    SELECT T1.SITE_ID, T1.COMPNY, T1.CUSTOMER_NO, T1.ZIP_GROUP, T1.LAT, T.LONG
    FROM
    SELECT Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG


    FROM MAS_ACCTtbl AS Q
    INNER JOIN LAT_LONG_FINAL AS T
    ON (Q.CUSTOMER_NO =T.CUSTOMER_NO) AND (Q.COMPNY =T.COMPANY) AND (Q.SITE_ID = T.SITE_ID)
    GROUP BY Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG ) AS T1
    INNER JOIN (
    SELECT R1.SITE_ID,
    R1.ZIP_GROUP , R1.LAT , R1.[LONG]
    FROM(
    SELECT R.SITE_ID , R.ZIP_GROUP , AVG(R.LAT) AS LAT_AVG , AVG(R.LONG) AS LONG_AVG
    FROM (
    SELECT Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG
    FROM MAS_ACCTtbl AS Q INNER JOIN LAT_LONG_FINAL AS T ON (Q.CUSTOMER_NO =T.CUSTOMER_NO) AND (Q.COMPNY =T.COMPANY) AND (Q.SITE_ID = T.SITE_ID)
    GROUP BY Q.SITE_ID, Q.COMPNY, Q.CUSTOMER_NO, Q.ZIP_GROUP, T.LAT, T.LONG) AS R
    GROUP BY R.SITE_ID , R.ZIP_GROUP )) AS R1
    ON T1.SITE_ID = R1.SITE_ID AND T1.ZIP_GROUP = R1.ZIP_GROUP

    can't sort out what access is looking for in the way of ( ). this is pretty straightforward in oracle or ibm. any ideas- rwhite7
    Last edited by rwhite7; 12-16-2014 at 06:06 PM.

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    I would suggest that you convert your subqueries to an actual queries and join the queries after.

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

Similar Threads

  1. Replies: 13
    Last Post: 05-30-2014, 04:07 PM
  2. Replies: 2
    Last Post: 01-29-2014, 01:28 PM
  3. Replies: 11
    Last Post: 11-08-2013, 06:14 PM
  4. INNER JOIN and WHERE clause problems
    By kman42 in forum Queries
    Replies: 2
    Last Post: 08-04-2011, 11:29 AM
  5. LEFT JOIN Breaks with Where Clause
    By jascraig in forum Queries
    Replies: 3
    Last Post: 08-31-2010, 08:56 AM

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