Filtering in Multi-Table Querry

[Guest]

I have a two tables linked together:

1. TaskTable
2. Assigned People Table

The task table has a one to many relationship with assigned people table.
The assigned table has engineers and responsible technical managers (RTMs).

Someone has requested that I show only the positions where a responsible
technical manager is not assigned. I created a query combining the two
tables. Here is an example of the results:

Position 1 Engineer 1
Position 1 Engineer 2
Position 1 RTM
Position 2 Engineer 1
Position 2 Engineer 2

If I query out the responsible technical managers, I still get the position
but with the engineers listed:

Position 1 Engineer 1
Position 1 Engineer 2
Position 2 Engineer 1
Position 2 Engineer 2

I should only see Position 2, since Position 1 does have an RTM.

How do I do this?

Thanks in advance.

 

[Guest]

This will take a total of 3 queries.

The first will just be used to join the two tables.

The second will be based on the first query and filter out all positions
except the RTMs. This will return a list of tasks that have RTMs

The third query will be based on joining the Task Table and the second query
by task. Include the Task field from the Table and the second query. Filter
the second query task field for Is Null. When that field is Null, that means
there is no assigned RTM.

 

[Guest]

Query tasksWithRTM selects out those with RTMs from assignedPeople table:

SELECT assignedPeople.taskID, assignedPeople.person
FROM assignedPeople
WHERE (((assignedPeople.person)="RTM"));

This query finds tasks with no RTM

SELECT taskTable.task
FROM taskTable LEFT JOIN TasksWithRTM ON taskTable.id = TasksWithRTM.taskID
WHERE (((TasksWithRTM.taskID) Is Null));