Why wont this LEFT JOIN work?

[105ben]

This query wont work with a LEFT JOIN, I get a "JOIN expression not supported" alarm... Works fine with an INNER though...

Basically, not every staff member has an allocation to a team, but I still want to see their records. Maybe theres another way to do this if access doesnt support Left Joins?

SELECT staff.EIN, staff.full_name, staff.email_address, role.role_name, team.team_name
FROM (((
staff
INNER JOIN role
ON staff.role_id = role.role_id
)
LEFT JOIN allocation_to_team
ON staff.EIN = allocation_to_team.EIN
)
INNER JOIN team
ON allocation_to_team.team_id = team.team_id
)
WHERE (((staff.EIN)=[Forms]![frmSearchUser]![cmbUser]));

 

[Guus2005]

you have to remove all parenthesis.
you only need them when you have a subquery.

hth

 

[105ben]

Thanks!!

Still getting a syntax error though

SELECT staff.EIN, staff.full_name, staff.email_address, role.role_name, team.team_name
FROM staff
INNER JOIN role ON staff.role_id = role.role_id
LEFT JOIN allocation_to_team ON staff.EIN = allocation_to_team.EIN
INNER JOIN team ON allocation_to_team.team_id = team.team_id
WHERE (((staff.EIN)=[Forms]![frmSearchUser]![cmbUser]));

in the error message, it highlights what Ive put in bold as being the error.

Anyone see why? IO cant

 

[pr2-eugin]

You cannot perform a LEFT JOIN after performing an INNER JOIN.. Easier way is to create a new Query (using the Query Design), add the four tables.. team, allocation_to_team, staff, role.. Then first step, create the LEFT JOIN.. between staff and allocation_to_team.. Then perform all INNER JOIN.. Finally add all required fields..

 

[105ben]

thanks for the advice, doesnt seem to have worked though

SQL looks like this after following that:

SELECT staff.EIN, staff.full_name, staff.email_address, role.role_name, team.team_name
FROM team INNER JOIN ((staff INNER JOIN role ON staff.role_id = role.role_id) LEFT JOIN allocation_to_team ON staff.EIN = allocation_to_team.EIN) ON team.team_id = allocation_to_team.team_id;

I did however delete all my joins in the query, then recreate them this time round, starting with the left join. Shouldnt make a difference though?

 

[jdraw]

Can you tell us what you want the query to do, in plain English?

 

[105ben]

Yeah sure.

I want it to return the fields:

staff.EIN, staff.full_name, staff.email_address, role.role_name, team.team_name

for the record in the staff table with a certain EIN (which is the primary key) and the user picks this EIN from the combo box located here [Forms]![frmSearchUser]![cmbUser]

then it needs to also get some information from the role table , and also the team table (however there is another table inbetween these called allocation to team, as a team member can be part of many team, or no teams... that is why the LEFT JOIN is needed)