Query results for records that do not have selected value

[MeSteve]

I am trying to make a query that will return which projects we have that do
not have a project manager assigned (RoleID = 1). I have 3 tables

tbl_Projects
ProjectID (PK)

tbl_Roles
RoleID (PK)

tbl_ContactProjects
ContactID (FK)
ProjectID(FK)
RoleID

Thanks.

 

[Michel Walsh]

Define a query listing the ProjectID values with a RoleID = 1:



SELECT DISTINCT ProjectID
FROM ContractProjects
WHERE RoleID=1




save that query, say, under the name of ProjectWithoutManager.

Next, run the query wizard about finding unmatched records: those in
Projects NOT having a match in ProjectWithoutManager. The produced SQL
should look like:



SELECT Projects.ProjectID
FROM Projects LEFT JOIN ProjectWithoutManager
ON Projects .ProjectID=ProjectWithoutManager.ProjectID
WHERE ProjectWithoutManager.ProjectID IS NULL




Hoping it may help,
Vanderghast, Access MVP

 

[Marshall Barton]

I am trying to make a query that will return which projects we have that do
not have a project manager assigned (RoleID = 1). I have 3 tables

tbl_Projects
ProjectID (PK)

tbl_Roles
RoleID (PK)

tbl_ContactProjects
ContactID (FK)
ProjectID(FK)
RoleID

I think this might be what you are asking for:

SELECT P.ProjectID
FROM tbl_Projects As P
LEFT JOIN (SELECT CP.ProjectID
FROM tbl_ContactProjects As CP
WHERE CP.RoleID = 1) As X
ON P.ProjectID = X.ProjectID
WHERE X.ProjectID Is Null