Unsupported Join in Access 2003

[anthonysjo]

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!

 

[Van T. Dinh]

You have to specify Inner Join or Left Join or Right Join. "Join" by it
selft deosn't work in JET SQL.

HTH
Van T. Dinh
MVP (Access)

 

[anthonysjo]

Thanks for the reply!

First off what is selft?

Secondly how would you reccommned changing the following SQL to make it work
correctly?
JOIN MSP_CODE_FIELDS AS MCF1 ON (MCF1.CODE_FIELD_ID=188744589) AND

(MCF1.PROJ_ID = MWP.PROJ_ID)

In access I have been making this an INNER JOIN and it doesn't like the
(MCF1.CODE_FIELD_ID=188744589) statement. If I remove it, the query works
but then I don't get the correct results.

You have to specify Inner Join or Left Join or Right Join. "Join" by it
selft deosn't work in JET SQL.

HTH
Van T. Dinh
MVP (Access)

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!

 

[Van T. Dinh]

"it selft" is a typing mistake. It should have been "itself".

AFAIK, the part:

(MCF1.CODE_FIELD_ID=188744589)

should be in the WHERE clause, not in the ON clause of the join.

If you are not familiar with JET SQL, Isuggest you try using the QBE (Query
By Example) grid in Access and see the SQL String generated by the QBE.

Also, check the JET SQL Reference section of Access Help to get a feel of
JET SQL.

 

[anthonysjo]

I was able to turn the (MCF1.CODE_FIELD_ID=188744589) statment into a where
clause last week but the part that is critical is the following statement:

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)

Any ideas? I am pretty sure they all have to be done in one query because
of the way the MS project tables are designed.

 

[Van T. Dinh]

I would put the part:

MOC1.PROJ_ID = (select PROJ_ID from MSP_PROJECTS where PROJ_TYPE=2)

out in the WHERE clause and not in the ON clause. AFAIK, ON clause is
normally use to specify some sort of corrspondence between (expressions
involving) Fields between the 2 Tables in the Join.

 

[Van T. Dinh]

I have just realised that you must be familiar with another flavour of SQL
which is probably ANSI-92 compliant. In this case, you may want to select
the "ANSI-92 compliant" option for your database which will allow you to use
what you already know from the other SQL flavour.