Left Join with Multiple Join Issue

[jenlivesay]

I've created a query to bring in all of P table and any associated E data from another table where available. Running the query using a single join works fine. Once I add in the additional join(s) I get a syntax error and after searching and searching I havent been able to determine where the error is. Also, if there is a better way, I am certainly open to suggestions. Any help is appreciated.

SELECT
P.TEAM_PM__C,
E1.NAME AS PM_NAME,
E1.EMPLOYEE_NUMBER__C AS PM_BADGE,
P.TEAM_DCC__C,
E2.NAME AS DCC_NAME,
E2.EMPLOYEE_NUMBER__C AS DCC_BADGE,
P.TEAM_CONVERSION_ANALYST__C,
E3.NAME AS CA_NAME,
E3.EMPLOYEE_NUMBER__C AS CA_BADGE,
P.ASSETS__C
FROM PLAN_PROJECT__C AS P
LEFT JOIN EMPLOYEE__C AS E1 ON (P.TEAM_PM__C=E1.ID)
LEFT JOIN EMPLOYEE__C AS E2 ON (P.TEAM_CONVERSION_ANALYST__C=E2.ID)
LEFT JOIN EMPLOYEE__C AS E3 ON (P.TEAM_DCC__C=E3.ID);

 

[CJ_London]

looks like your brackets in your join code or they are in the wrong places, try

 FROM ((PLAN_PROJECT__C AS P
LEFT JOIN EMPLOYEE__C AS E1 ON P.TEAM_PM__C=E1.ID)
LEFT JOIN EMPLOYEE__C AS E2 ON P.TEAM_CONVERSION_ANALYST__C=E2.ID)
LEFT JOIN EMPLOYEE__C AS E3 ON P.TEAM_DCC__C=E3.ID;

always worth mocking this up in the query builder to check the syntax

Also I note you have NAME as one of your field names. Name is a reserved word and can cause issues

 

[jenlivesay]

Thanks! Changing the parenthesis worked perfectly! Unfortunately, I couldnt get the builder to work the way I needed it to with the multiple joins, but it's probably my lack of experience using the Access builder.

 

[CJ_London]

couldnt get the builder to work the way I needed it to with the multiple joins

You'll be able to view that code in the query builder to see what it looks like

To see how the join is 'configured' double click on it to open the properties to see the options

You can also set things like aliases by displaying properties and selecting the appropriate table