Using subquery in a join statement in ADO SQL Excel VBA

[pcanino]

Hi everyone - if anyone can figure out what is wrong with my sqlstatement here because I'm really out of ideas on how to resolve this, i have scoured the net for days already.

Here's the query:
SELECT Sup.[SAP ID] FROM [Supply$] AS <sup>LEFT JOIN [Demand$] AS [Dem] ON (Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]))

I'm executing this in Excel and I get this error:
"[Microsoft][ODBC Excel Driver] Syntax error in query expression 'Sup.[Career Level] IN (SELECT [Range Level 1] FROM [Matrix$] AS [M] WHERE M.[Range Level] = Dem.[Career Level]'."</sup>

 

[Kyle123]

It should be more efficient than the correlated query, but since you seem to be using Excel as a data source, all bets are off. You aren't querying open workbooks are you?

 

[pcanino]

Thanks a lot Kyle.. It did help how can I close this thread by the way?

 

[Kyle123]

You can't on this forum, glad it helped