leftjoin trouble i think!

[ice-9]

Hi,
im realy realy stuck this time.
This is the problem:

i have 2 tables. groupproject, employeproject.

Groupproject has: groupcode(pk), employeCode(pk), projectCode(pk)
employeeGroup has: employeeCode(pk) and projectCode(pk)

a project can have a group and a group can have members. A member is related to a project. Now i want to display all the members who are not in the selected group . For example, i select group 1 and i display all the members who are not in 1. The problem i keep have is that for example:

employee 1 is on group 1 and in group 2 and goup 1 is selected, he wil be show. This is because i use a statment "<> selectedgroup" . So the query runs trough te tables, comes back with group 1 and says, ok employee 1 is in the group i will not show. BUT THEN the query comes to eployee 1 in group 2 and the query says, hey your not in group 1, i throw you on the list.

can someone help me with this irritating issue.
i tried this query

SELECT ProjectGroup.ProjectCode, ProjectGroup.GroupCode, ProjectGroup.EmployeeCode FROM ProjectMembers LEFT JOIN ProjectGroup ON (ProjectMembers.ProjectCode=ProjectGroup.ProjectCode) AND (ProjectMembers.EmployeeCode=ProjectGroup.EmployeeCode) WHERE (((ProjectGroup.ProjectCode)=Forms!GroupView!ProjectCombo) And ((ProjectGroup.EmployeeCode) Is Null)) Or (((ProjectGroup.GroupCode)<>Forms!GroupView!GroupView));

 

[Brian1960]

I am slightly confused. Do you mean FK (Foreign Keys)?

PojectGroup or GroupProject???

Get away from thinking left join etc access will happily with with SQL.

Select employee from employees where (empoyeeID not in (select employeeID from Group where Group = 1) and emplyeeID in (select employeeID from projectgroup))

This lsits all employees whose ID is not in the group1 but is in the project .

 

[ice-9]

projectgroup
and fk

 

[ice-9]

i have this atm
SELECT ProjectMembers.EmployeeCode, ProjectMembers.ProjectCode
FROM ProjectMembers
WHERE (((ProjectMembers.EmployeeCode) Not In ((select ProjectGroup.EmployeeCode from ProjectGroup where ProjectGroup.GroupCode)<>[Forms]![GroupView]![GroupView])) AND ((ProjectMembers.ProjectCode)=[Forms]![GroupView]![ProjectCombo]));

but this displays all the members in a group. I want just the members who arnt in the choosen group. A member can be in more groups. So again the same problem exists. for example: eployee 1 is in 2 groups. group 1 and 2. the query looks and says employee 1 is in group 1 so i dont put him in the list. Then i checks group 2, and sees hey eployee 1 is in this list, but group 1 is selected so i put him on the list.

hope i made clear what the problem is.