Excel connection query error

  • In the connection properties I have the following entered into the Command Text:


    SQL
    SELECT 
    *
    FROM  `Path.accdb`.ddqAnswers ddq
    LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
    ON ddq.Partner = swft.PartnerID


    This works fine.


    This also works fine:


    SQL
    SELECT 
    *
    FROM  `Path.accdb`.ddqAnswers ddq
    LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
    ON ddq.Partner = cut.PartnerID


    However, this creates an error:


    SQL
    SELECT 
    *
    FROM  `Path.accdb`.ddqAnswers ddq
    LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
    ON ddq.Partner = cut.PartnerID
    LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
    ON ddq.Partner = swft.PartnerID


    Anyone know why?

  • Re: Excel connection query error


    You should state what the error you get is.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Excel connection query error


    good point

    Quote


    [microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ddq.Partner = cut.PartnerID LEFT JOIN 'Path.accdb'.tblDdqSwiftMessaging swft ON cut.PartnerID = swft.PartnerI'.

  • Re: Excel connection query error


    I came across this problem again... it seems as long as I do ONE join, it works fine. As soon as I add a second join, I get an error. I would think that the syntax would be the same regardless of how many joins you use.

  • Re: Excel connection query error


    Actually, just solved it. The correct format is:



    SQL
    SELECT * FROM ( [TABLE1] a
    LEFT JOIN [TABLE2] b ON a.[Fieldname] = b.[Fieldname] )
    LEFT JOIN [TABLE3] c ON a.[Fieldname] = c.[Fieldname]


    I'm assuming that for each additional JOIN, you would need a new set of parenthesis so that you only ever have on join outside of the parenthesis, however I have not tested yet.

  • Re: Excel connection query error


    seems strange...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Excel connection query error


    yes, but ive found that Microsoft products can be strange. I don't question it anymore, just try to solve one problem at a time ha.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!