Access query error "JOIN Expression not supported" - nearly pulled all my hair out!

[policechiefwiggum]

Hi All,

Ok, this has nearly make me go bald from pulling my hair out!
I have a bunch of SQL that is run in Excel that i need to move to Access. Most of them are working fine, but this one is really causing me problems!!

SELECT 
x.SENTITYFAMILY AS [Entity Family], 
a.SENTITY AS [Entity], 
c.SENTITYSTATEMEMBER AS [State], 
e.SENTITYSTATEACTION AS [Action1], 
c.BDEFAULT AS [Default], 
e.bUIVISIBLE AS [UI Visible], 

IIf(Not [h].[sExtension] Is Null And Not [k].[SentityStateMember] Is Null,[h].[sExtension] & " (" & [i].[SDEFINITIONNAME] & ")",
IIf(Not [h].[sExtension] Is Null And Not [p].[SVIEW] Is Null,[h].[sExtension] & " (" & [p].[SVIEW] & ")",
IIf(Not [h].[sExtension] Is Null And [k].[SentityStateMember] Is Null,[h].[sExtension],
IIf(Not [m].[SENTITY] Is Null,"Related Task List - " & [n].[SENTITYSTATEACTION] & " (" & [m].[SENTITY] & ")",""))))
AS [Action], [g].[bConditional] AS Conditional

FROM
((((((((dbo_ENTITYFAMILIES as x
Inner join dbo_ENTITY as a on x.LENTITYFAMILYKEY = a.LENTITYFAMILYKEY)
Inner join dbo_ENTITYSTATES as b on b.LENTITYSTATEKEY = a.LENTITYSTATEKEY)
Inner join dbo_ENTITYSTATEMEMBERS as c on b.LENTITYSTATEKEY = c.LENTITYSTATEKEY)
Inner join dbo_ENTITYSTATEMEMBERACTIONS as d on c.LENTITYSTATEMEMBERKEY = d.LENTITYSTATEMEMBERKEY)
Inner join dbo_ENTITYSTATEACTIONS as e on d.LENTITYSTATEACTIONKEY = e.LENTITYSTATEACTIONKEY)
Inner join dbo_TaskLists as f on e.LTASKLISTKEY = f.lTaskListKey)
Inner join dbo_TaskListItems as g on f.lTaskListKey = g.lTaskListKey)
left join dbo_ARCHITECTUREEXTENSIONS as h on g.lArchitectureExtensionKey = h.LARCHITECTUREEXTENSIONKEY)
left join dbo_TASKSAUTOSTATECHANGE as i on (h.sV2ServerExtensionType = "AutoStateChange" AND g.lArchitectureExtensionTaskKey = i.LTASKSAUTOSTATECHANGEKEY))
left join dbo_TASKSAUTOSTATECOMP as j on i.LTASKSAUTOSTATECHANGEKEY = j.LTASKSAUTOSTATECHANGEKEY)
left join dbo_ENTITYSTATEMEMBERS as k on j.LTARGETSTATEMEMBERKEY = k.LENTITYSTATEMEMBERKEY)
left join dbo_Entity as m on g.lREntityKey = m.LENTITYKEY)
left join dbo_ENTITYSTATEACTIONS as n on g.lREntityStateActionKey = n.LENTITYSTATEACTIONKEY)
left join dbo_TASKSFORCEDVIEWLOADS as o on [h.sV2ServerExtensionType =" & "'ForcedViewLoad'"  AND  g.lArchitectureExtensionTaskKey = o.LTASKSFORCEDVIEWLOADSKEY])
left join dbo_ENTITYVIEWS as p on (o.LENTITYVIEWKEY = p.LENTITYVIEWKEY)
order by dbo_SENTITYFAMILY, a.SENTITY, c.NBIT;

it seems to work fine until it gets to

left join dbo_TASKSAUTOSTATECHANGE as i on (h.sV2ServerExtensionType = "AutoStateChange" AND g.lArchitectureExtensionTaskKey = i.LTASKSAUTOSTATECHANGEKEY))

i've tried square brackets, round brackets, no brackets everything i can think of, but keep getting JOIN Expression not supported

Any help is appreciated.

Many Thanks
Pete

 

[SkipVought]

hi,

In your FROM clause you have 10 left parentheses and 16 right parentheses.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[policechiefwiggum]

So I have, Thanks SkipVought.

Updated, but still getting the same issue!

FROM
((((((((((((((dbo_ENTITYFAMILIES as x
Inner join dbo_ENTITY as a on x.LENTITYFAMILYKEY = a.LENTITYFAMILYKEY)
Inner join dbo_ENTITYSTATES as b on b.LENTITYSTATEKEY = a.LENTITYSTATEKEY)
Inner join dbo_ENTITYSTATEMEMBERS as c on b.LENTITYSTATEKEY = c.LENTITYSTATEKEY)
Inner join dbo_ENTITYSTATEMEMBERACTIONS as d on c.LENTITYSTATEMEMBERKEY = d.LENTITYSTATEMEMBERKEY)
Inner join dbo_ENTITYSTATEACTIONS as e on d.LENTITYSTATEACTIONKEY = e.LENTITYSTATEACTIONKEY)
Inner join dbo_TaskLists as f on e.LTASKLISTKEY = f.lTaskListKey)
Inner join dbo_TaskListItems as g on f.lTaskListKey = g.lTaskListKey)
left join dbo_ARCHITECTUREEXTENSIONS as h on g.lArchitectureExtensionKey = h.LARCHITECTUREEXTENSIONKEY)
left join dbo_TASKSAUTOSTATECHANGE as i on (h.sV2ServerExtensionType = "AutoStateChange" AND g.lArchitectureExtensionTaskKey = i.LTASKSAUTOSTATECHANGEKEY))
left join dbo_TASKSAUTOSTATECOMP as j on i.LTASKSAUTOSTATECHANGEKEY = j.LTASKSAUTOSTATECHANGEKEY)
left join dbo_ENTITYSTATEMEMBERS as k on j.LTARGETSTATEMEMBERKEY = k.LENTITYSTATEMEMBERKEY)
left join dbo_Entity as m on g.lREntityKey = m.LENTITYKEY)
left join dbo_ENTITYSTATEACTIONS as n on g.lREntityStateActionKey = n.LENTITYSTATEACTIONKEY)
left join dbo_TASKSFORCEDVIEWLOADS as o on (h.sV2ServerExtensionType = "ForcedViewLoad"  AND  g.lArchitectureExtensionTaskKey = o.LTASKSFORCEDVIEWLOADSKEY))
left join dbo_ENTITYVIEWS as p on (o.LENTITYVIEWKEY = p.LENTITYVIEWKEY)
order by dbo_SENTITYFAMILY, a.SENTITY, c.NBIT;

 

[SkipVought]

Not knowing your data or relationships, does it matter that in the highlighted instance the field names don't seem to correlate as they do in most other joins???
[tt]
g.lArchitectureExtensionTaskKey = i.LTASKSAUTOSTATECHANGEKEY
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]