MS Access query with no duplicates

Question

I'm working on building a query where it doesn't allow for duplicates. I've built a task/time manager and in the table for it I created a column called "Active". It's a true/false switch that signifies if an employee is busy or free. I'm now building a query where I want it to show if "active" switch is true then it shows as "Busy" otherwise if it's false they are "Free". I can get this to work but it shows duplicates. In the table I do have it indexed to not show duplicates but I'm still getting them.

Here is the SQL View:

 `SELECT Employees.Employee
 , IIf([Active]=True,"Unavailable","Free") AS Avail
 FROM tblTasks INNER JOIN Employees ON tblTasks.[User ID] = Employees.UserID;`

This is the current setup:

And this is the current output:

Ultimately the goal is to get it so that if any employee has a "True" flag on the "Active" field then they appear once as "Unavailable" but if they have all "False" tags on the Active field they appear once as "Available".

Any help would be greatly appreciated.

Answer 1

Options:

SELECT Employees.UserID, Employee, IIf(CntTrue>0, "Unavailable", "Available") AS Status 
FROM Employees 
LEFT JOIN (SELECT Count(*) AS CntTrue, UserID FROM tblTasks 
           WHERE Active = True GROUP BY UserID) AS Q1 
ON Employees.UserID = Q1.UserID;

or

SELECT Employees.*, IIf(Q1.UserID Is Null, "Available", "UnAvailable") AS Status 
FROM Employees 
LEFT JOIN (SELECT DISTINCT UserID FROM tblTasks WHERE Active = True) AS Q1 
ON Employees.UserID = Q1.UserID;

or

SELECT UserID, Employee, IIf(DCount("*","tblTasks","Active=True AND UserID=" & [UserID])>0, "Unavailable", "Available") AS Status FROM Employees;

Note use of LEFT JOIN to assure all employees are returned. INNER JOIN would exclude employees who are not assigned a task. Perhaps all employees have at least one task but this allows for possibility some don't.

Advise not to use spaces in naming convention.
Should probably designate UserID in Employees as primary key.