Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

policechiefwiggum

Programmer
Mar 19, 2006
48
GB
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!!

Code:
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
Code:
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
 
hi,

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
So I have, Thanks SkipVought.

Updated, but still getting the same issue! :(

Code:
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;

10945654013_cf60dd0061.jpg


10945564904_806e69a543.jpg
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top