Excel connection query error

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

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

This works fine.

This also works fine:

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

However, this creates an error:

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

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:

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

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.