CODEWITHSUNDEEP
vbadatabasems-access
Deke
Deke

Reputation: 495

MS Access query with no duplicates

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: CurrentSetup

And this is the current output:

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.

Upvotes: 0

Views: 5530

Answers (3)

Larry
Larry

Reputation: 1

I have found something which may make it easier. In the query using "design view" right-click on the upper portion of the query (where the tables you draw from are located) and pull up the "properties". In the properties box (usually located on the right side change "Unique Values" to yes. Let us know if this works for you.

Upvotes: 0

June7
June7

Reputation: 21370

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.

Upvotes: 2

forpas
forpas

Reputation: 164099

This will work:

select 
  e.employee,
  max(iif(t.active, "Unavailable", "Free"))
from Employees e inner join tblTasks t
on t.userid = e.userid
group by e.userid, e.employee

since the string "Unavailable" is greater than the string "Free" (or "Available").
If you choose different strings to denote availability, you will have to adjust.
So if you choose "Busy" and "Free" then you must change to:

select 
  e.employee,
  min(iif(t.active, "Busy", "Free"))
from Employees e inner join tblTasks t
on t.userid = e.userid
group by e.userid, e.employee

Upvotes: 0

Related Questions