Thread: Access Query Is a Supervisor

Good afternoon...Access Guru's!!!!

I have an access db I'm working in, I'm working off one table pulled in a query grid twice. The fields I'm working with are: Active Status (A or T, EmployeeID (4 or 5 digits), Last Name, ISaSupervisor (Y or N), SupervisorID (4 or 5 digits).

I can write the query to list all employees that are supervisors and there direct employees, no problem here. My question is doing the reverse, list every supervisor that has NO Direct reports.

Here's my query that shows all active supervisors and their active employees:

Code:

SELECT PR_MAST.Active_Stat, PR_MAST.Emp_No, PR_MAST.Issuper, PR_MAST.Last_Name, PR_MAST_1.Emp_No, PR_MAST_1.Active_Stat, PR_MAST_1.Last_Name
FROM PR_MAST INNER JOIN PR_MAST AS PR_MAST_1 ON (PR_MAST.Emp_No = PR_MAST_1.Empsuper) AND (PR_MAST.Loc_No = PR_MAST_1.Loc_No)
WHERE (((PR_MAST.Active_Stat)="A") AND ((PR_MAST_1.Active_Stat)="A"));

Again, I'm looking to modify this query to show me active supervisors that have no direct reports. Any help would be great!!

OK, so working on the assumption that Emp_No is the primary key for the table I think you want something like:

Code:

SELECT PR_MAST.Active_Stat, PR_MAST.Emp_No, PR_MAST.Issuper, PR_MAST.Last_Name
FROM PR_MAST LEFT JOIN PR_MAST AS PR_MAST_1 ON (PR_MAST.Emp_No = PR_MAST_1.Empsuper) AND (PR_MAST.Loc_No = PR_MAST_1.Loc_No)
WHERE (((PR_MAST.Active_Stat)="A") AND ((PR_MAST_1.Emp_No) Is Null));

This sets both joins to the type "Include ALL records from 'PR_MAST' and only those records from 'PR_MAST_1' where the joined fields are equal.". Then the criteria PR_MAST_1.Emp_No Is Null makes the query ignore any records in PR_MAST that have a match in PR_MAST_1, leaving you with only those that do not have a match.

If you still require the criteria PRMAST_1.Active_Stat = "A" as well, I would make a second query:

Code:

SELECT * FROM PR_MAST WHERE PR_MAST.Active_Stat = "A";

Then bring this query into your existing query in place of PR_MAST_1.