Results 1 to 7 of 7
  1. #1
    Kentaro is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    5

    Question 2016 "JOIN expression not supported" when using LEFT JOIN

    I am working to create a children's list.
    I wrote as follows, but there is an error.
    Please tell me the problem. In addition, Please fix.



    SELECT MM.ID, MM.grade,MM.suname, MM.name,MM.tel,
    S1.grade AS bro_sis_grade1, S1.name AS bro_sis_name1,
    S2.grade AS bro_sis_grade2, S2.name AS bro_sis_name2,
    S3.grade AS bro_sis_grade3, S3.name AS bro_sis_name3,
    FROM
    (
    (
    (
    T_students AS MM
    LEFT JOIN T_students AS S1
    ON (S1.tel = MM.tel AND S1.grade <> MM.grade)
    )
    LEFT JOIN T_students AS S2


    ON (S2.tel = MM.tel AND S2.grade <> MM.grade AND S2.grade <> S1.grade)
    )
    LEFT JOIN T_students AS S3
    ON (S3.tel = MM.tel AND S3.grade <> MM.grade AND S3.grade <> S1.grade AND S3.grade <> S2.grade)
    )
    ;

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It might he helpful if you could post a small sample of what the data in each table looks like, and what your expected result should look like. Then we can try to recreate the scenario and see if we can come up with the SQL code you need.

  3. #3
    Kentaro is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    5
    Thank you for your reply.
    Now, because it is in the work, I would like later to show a table.


    *English is not a native. For that reason, Excuse me in strange English.

  4. #4
    Kentaro is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    5
    Thank you for your cooperation.


    Code:
    sample table (T_students)
    
    ID  grade  suname  name  tel
    -----------------------------------------
     1      1             A     A'    1111-11-1111
     2      2             B     B'    2222-22-2222
     3      3             A     A''   1111-11-1111
     4      4             B     B''   2222-22-2222
     5      5             A     A'''  1111-11-1111
     6      6             D     D'   3333-33-3333
    
    
    
    
    expected result
    
    
    ID  grade  suname  name  tel             bro_sis_grade1  bro_sis_name1  bro_sis_grade2  bro_sis_name2  bro_sis_grade3  bro_sis_name3
    ------------------------------------------------------------------------------------------------------------------------------------
     1      1             A     A'    1111-11-1111                3                   A''                     5                  A'''
     2      2             B     B'    2222-22-2222                4                   B''
     3      3             A     A''   1111-11-1111                1                   A'                      5                  A'''
     4      4             B     B''   2222-22-2222                2                   B'
     5      5             A     A'''  1111-11-1111                1                   A'                      3                  A''
     6      6             D     D'   3333-33-3333

  5. #5
    Kentaro is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    5
    It was shifted.
    Excuse me.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Well, one issue you have is you have a comma after the last field in your SELECT clause before the FROM word. There should not be a comma there.

    And I think you have problem in your link when you try to compare things like:
    Code:
    S2.grade <> S1.grade
    Since S1 and S2 are not linked to each other (and pieces may actually be NULL in some cases due to the left joins).

    I would recommend doing it one step at a time (adding just one join at a time), and get that part to work before moving on to the next piece. Start small, and work your way up.

  7. #7
    Kentaro is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    5
    Quote Originally Posted by JoeM View Post
    And I think you have problem in your link when you try to compare things like:
    Code:
    S2.grade <> S1.grade
    Since S1 and S2 are not linked to each other (and pieces may actually be NULL in some cases due to the left joins).
    Thank you.


    The above part. I was not able to clear by all means.


    Including the VBA, I will be reconsider.

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

Similar Threads

  1. RIGHT join expression not supported?
    By ittechguy in forum Queries
    Replies: 3
    Last Post: 11-12-2015, 11:00 PM
  2. Join expression not supported
    By Bschmale in forum Access
    Replies: 23
    Last Post: 02-12-2015, 12:09 PM
  3. Replies: 3
    Last Post: 05-15-2013, 02:49 PM
  4. "Join expression not supported"?
    By jsoldi in forum Queries
    Replies: 1
    Last Post: 08-29-2011, 01:21 PM
  5. Join Expression not Supported
    By amegahed3 in forum Queries
    Replies: 6
    Last Post: 09-29-2010, 01:28 PM

Tags for this Thread

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