Unsupported Join in Access 2003

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello all,

I am connecting to a Project Server database using Access via an ODBC
connection and I have been able to figure out how to write most of my queries
to work how I want. However, I have one query that I built in SQL Query
Analyzer that I can't figure out how to make work in Access. Below is the
query that was built in QA:

SELECT EP.PROJECT_ID as ID, EP.PROJECT_NAME as Name, MOC1.OC_NAME AS
[EPOC1], MOC2.OC_NAME AS [EPOC2], MOC3.OC_NAME AS [EPOC3], MOC4.OC_NAME AS
[EPOC4]

FROM EPKP_PROJECTS AS EP

join MSP_WEB_PROJECTS AS MWP ON (EP.WPROJ_ID = MWP.WPROJ_ID)

JOIN MSP_CODE_FIELDS AS MCF1 ON (MCF1.CODE_FIELD_ID=188744589) AND
(MCF1.PROJ_ID = MWP.PROJ_ID)

left JOIN MSP_OUTLINE_CODES AS MOC1 ON MOC1.CODE_UID = MCF1.CODE_UID AND
MOC1.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

left JOIN MSP_CODE_FIELDS AS MCF2 ON (MCF2.CODE_FIELD_ID=188744590) AND
(MCF2.PROJ_ID = MWP.PROJ_ID)

left JOIN MSP_OUTLINE_CODES AS MOC2 ON MOC2.CODE_UID = MCF2.CODE_UID AND
MOC2.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

left JOIN MSP_CODE_FIELDS AS MCF3 ON (MCF3.CODE_FIELD_ID=188744591) AND
(MCF3.PROJ_ID = MWP.PROJ_ID)

left JOIN MSP_OUTLINE_CODES AS MOC3 ON MOC3.CODE_UID = MCF3.CODE_UID AND
MOC3.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

left JOIN MSP_CODE_FIELDS AS MCF4 ON (MCF4.CODE_FIELD_ID=188744592) AND
(MCF4.PROJ_ID = MWP.PROJ_ID)

left JOIN MSP_OUTLINE_CODES AS MOC4 ON MOC4.CODE_UID = MCF4.CODE_UID AND
MOC4.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

where EP.WPROJ_ID > 0

Specifically, the join that seems to be unsupported is as follows:
(MCF1.CODE_FIELD_ID=188744589)
or
MOC1.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

Any help would be greatly appreciated.

Thanks Anthony!

.

Relevant Pages

  • Re: Unsupported Join in Access 2003
    ... selft deosn't work in JET SQL. ... > I am connecting to a Project Server database using Access via an ODBC ... I have one query that I built in SQL Query ...
    (microsoft.public.access.queries)
  • VB6-ADO-COM-ODBC-INFORMIX : Problem running query. SQL State 37000
    ... Connecting to a Informix 7.3 Database located on a Unix Server. ... Above query produces this error.... ... Microsoft OLE DB Provider for ODBC Drivers. ... ODBC Driver details are below.... ...
    (microsoft.public.data.ado)
  • VB6-ADO-COM-ODBC-INFORMIX-OLEDB - SELECT Query Err. SQLState 37000
    ... Connecting to a Informix 7.3 Database located on a Unix Server. ... Above query produces this error.... ... Microsoft OLE DB Provider for ODBC Drivers. ... ODBC Driver details are below.... ...
    (microsoft.public.data.oledb)
  • VB6-ADO-COM-ODBC-INFORMIX : Queries produce SQL State 37000.
    ... Connecting to a Informix 7.3 Database located on a Unix Server. ... Above query produces this error.... ... Microsoft OLE DB Provider for ODBC Drivers. ... ODBC Driver details are below.... ...
    (microsoft.public.data.odbc)
  • RE: db.execute INSERT INTO very slow
    ... Odd that this only happens on this one query on one computer. ... tried various configurations with different computers connecting the the temp ... use a form on startup that is bound to a table in the backend ... Other pc's on the network take sub 2 seconds. ...
    (microsoft.public.access.modulesdaovba)