Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Inner and Left join not working in passthroughs

    Hi all.

    I have re-writtena rather lengthy import procedure using SQL tables , excel sheets and accdb backend data (mission critical) that I inherited from another developer. i have streamlined the process from about 45 minutes down to roughly 10 minutes using all frontend side prcocessing (DAO and currentdb.execute statements)

    so now I wrote a great function that takes the strsql I using for my currentdb.execute strsql staments and saves them as a querydef.sql for a passthrough.

    The speed for the tested sql statements that are deletes, single table updates and insert into's from one table to another are just phenomenal but i am getting jammed up with the Access side inner join and left join.

    The message coming back is an error at .. and i know it is the syntax i am using for the inner join or left join.

    i have included two real world examples where the strsql being passed into the querydef for execution fails.

    Could someone please give me some ideas on how to write these statements syntactically correct?

    Also if anyone wants I have the pefect 16 line function that executes 'action" queries server side on sql.

    here is a snippett of the two case in point strsql statements i am passing through and fail:

    strSQL = "INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) " _
    & "SELECT [TASK Excel Data].[Activity ID], [TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], " _
    & "[TASK Excel Data].Area, [TASK Excel Data].Line " _
    & "FROM [TASK Excel Data] LEFT JOIN TASK_BackUP ON [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] " _
    & "WHERE TASK_BackUP.[Activity ID] Is Null"

    db.Execute strSQL, dbSeeChanges ' the currentdb.execut works but my function fnpassThroughs(strsql) fails

    strsql = "UPDATE TASK INNER JOIN [TASK Excel Data] " _
    & "ON TASK.[Activity ID] = [TASK Excel Data].[Activity ID] " _
    & "SET [TASK Excel Data].JCID = [TASK].[JCID], " _
    & " [TASK Excel Data].JobCardNumber = [TASK].[JobCardNumber]"

    db.execute strsql, dbSeeChanges

    lIKE i SAID IF ANYONE WANTS THE FUNCTION i WILL POST IT.

    Thank you in advance.
    Dale Houston, TX

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by axsprog View Post
    The message coming back is an error at .. and i know it is the syntax i am using for the inner join or left join.
    The exact error would be helpful!

    Without any DDL and bit of data to test, it is difficult to know exactly where the problem is and to test any solution. The following is untested.

    Looking at the the first query:

    Code:
    INSERT INTO NewTasksBeingAdded 
     ([Activity ID], [WBS Code], [Activity Name], Area, Line)
         (SELECT [TASK Excel Data].[Activity ID], [TASK Excel Data].[WBS Code],
                 [TASK Excel Data].[Activity Name],[TASK Excel Data].Area,
                 [TASK Excel Data].Line
            FROM [TASK Excel Data]
            LEFT JOIN TASK_BackUP 
              ON [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID]
           WHERE TASK_BackUP.[Activity ID] Is Null)
    Is NewTasksBeingAdded really the table name?
    I added ( ) around the Select statement.

    It is an interesting query in of itself. You are gathering all rows where the activityID's are equal between the 2 tables, but then limiting the result set to where the ActivityID is null in the joined table (task_Backup).

    Is that not basically all ActivityID's in [TASK Excel Data].ActivityID that are currently null?

    If I am not understanding fully what you are trying to do, let me know!

    On the 2nd query, I think the syntax was incorrect. Try this:

    Code:
    UPDATE TASK
       SET [TASK Excel Data].JCID = [TASK].[JCID]
         , [TASK Excel Data].JobCardNumber = [TASK].[JobCardNumber]
      FROM TASK
     INNER JOIN [TASK Excel Data] 
        ON TASK.[Activity ID] = [TASK Excel Data].[Activity ID]
    It does seem odd that you updating TASK, but the SET predicate is setting columns in the [TASK Excel Data] table.

    Hey, if it works. . . But it might be something to look at.

    HTH

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    UPDATE TASK
    SET [TASK Excel Data].JCID

    was a paste error it was suppose to be [TASK Excel Data]

    also the errors are syntax errors at the word Inner - TSQl does not use Inner Join - I need to research the workaround for join statements in SQL from what I understand I do not have to use joins I can simpley state
    something like
    select task.fname from task,
    task.id = subtask.id
    where subtask.id = 3"

    not a great example but that is the gist. I have an old Access developers reference manual at home I think I need to dive into.

    But without a doubt the syntax error is on the inner join and 99% sure the left join. Someone told me about a 'full join" to take the place of inner join but I need to look at it.
    Dale Houston, TX

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version of Microsoft Access are you using???

    INNER JOIN Operation (Microsoft Access SQL)*[Access 2007 Developer Reference]

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •