Results 1 to 4 of 4
  1. #1
    JimReid is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Location
    Erial , New Jersey
    Posts
    30

    Access SQL Multiple Joins

    I have the following sql statement:
    SELECT [tblPSSchedule].[ContractID]
    , [tblPSSchedule].[Route]
    , [tblPSSchedule].[SiteID]
    , [tblPSSchedule].[CodeID]
    , [tblPSSchedule].[Quantity]
    , [tblPSSchedule].[Renew]
    , [tblJobSite].[ServiceStartDate]
    , [tblJobSite].[SiteID]
    , [tblJobSite].[ZipCode]
    , [tblZip].[Zip]
    , [tblZip].[County]
    FROM tblPSSchedule
    LEFT JOIN [tblJobSite]
    ON [tblPSSchedule].[SiteID] = [tblJobSite].[SiteID]
    LEFT JOIN [tblZip]
    ON [tblJobSite].[ZipCode] = [tblZip].[Zip];



    I keep getting an error on the 2nd JOIN as follows:


    If I take the 2nd JOIN away it appears to work but I can't see anything wrong with the second JOIN. I put the brackets around the table names and field names to insure accuracy but still no luck. The odd thing about it is the error message is cutting off the last bracket on = [tblZip].[Zip]; I don't know if that is a message limitation or isn't showing the whole error. When I put double ]'s at the end it shows the 1 bracket with the same error so I don't think it is the brackets. If I take the brackets off it cuts off the last letter of Zip.

    It appears the error in in the second JOIN but I can't see anything wrong with it.

    Maybe another pair of eye would help. I didn't put the table definitions here because of their size but will if it would help.

    Thanks to all.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Was there supposed to be a pic of an error message there?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    What are structures of tblJobSite and tblZip? I.e. are fields [tblJobSite].[ZipCode] and [tblZip].[Zip] of same type (both string, or both numeric)?

    I suspect, the table tblZip has structure like tblZip: ZipCode, Zip, County, ... - when it is so, then you have to join through ZipCode from both of tables. When table tblZip doesn't have the field ZipCode, and table tblJobSite has field Zip too, then you have to use field Zip from both tables to join. When those tables don't have a common field at all, the no Join is possible. But anyway, the only reason to get error is non-matching data types! When data types are similar, but there is no matching values in Join fields, you get empty fields from one of tables, but not error!


  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Normally you get the explanation of the error + line number in the lower part of the SSMS window. See example:

    What is the message in your case?
    Attached Thumbnails Attached Thumbnails errorMessageSQL.JPG  

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

Similar Threads

  1. Replies: 4
    Last Post: 07-24-2020, 02:30 AM
  2. update with multiple joins
    By Davide in forum Queries
    Replies: 4
    Last Post: 07-03-2020, 04:06 AM
  3. Multiple joins will not work
    By Newtrick in forum Queries
    Replies: 1
    Last Post: 06-19-2019, 12:50 PM
  4. Multiple Inner Joins
    By comfygringo in forum Queries
    Replies: 5
    Last Post: 09-03-2013, 10:49 PM
  5. Replies: 6
    Last Post: 06-21-2013, 08:14 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