Removing duplicates in MS Access query
Experts:
In the query below, I have a criteria clauses to
(1) exclude terminated employees, pensioners, casuals and interns from a query of non-support employees A or
(2) include Terminated Employees but only if they have associated records in an allocations table B.
But the allocations table (table B) has multiple records per employee so I'm getting duplicates with the expression in the 2nd Where clause: AND ((B.clock_to) Is Not Null))
Suggestions?
In the query below, I have a criteria clauses to
(1) exclude terminated employees, pensioners, casuals and interns from a query of non-support employees A or
(2) include Terminated Employees but only if they have associated records in an allocations table B.
But the allocations table (table B) has multiple records per employee so I'm getting duplicates with the expression in the 2nd Where clause: AND ((B.clock_to) Is Not Null))
Suggestions?
SELECT A.Source, A.Status, A.EmployeeName, A.strClock, A.Category, A.Title, A.Program, A.Program2, A.Program3, A.Location, A.RecentHireDate, A.[Band], A.WorkPct, A.Supervisor, A.Supervisor2Name, A.Supervisor3Name, A.FLSA, A.EmpLevel, A.EmpLevelBud, A.HRPartner, A.TerminationDate, A.Budgeted, A.ReasonForVacancy, A.Replacing, A.PostingStatus, A.BusinessFunction
FROM (qAllNonSupport AS A LEFT JOIN tClockAllocations AS B ON A.strClock = B.clock_to) LEFT JOIN tEmpAttributesNonSupport AS C ON A.strClock = C.strClock
WHERE
(((A.Status)<>"Terminated"
OR (((A.Status)="Terminated" And (A.Status)="No") AND ((B.clock_to) Is Not Null))
OR (((A.strClock)="1539") AND ((C.SupportEligible)=Yes))
ORDER BY A.Source, A.EmployeeName;
TIA
FROM (qAllNonSupport AS A LEFT JOIN tClockAllocations AS B ON A.strClock = B.clock_to) LEFT JOIN tEmpAttributesNonSupport AS C ON A.strClock = C.strClock
WHERE
(((A.Status)<>"Terminated"
OR (((A.Status)="Terminated" And (A.Status)="No") AND ((B.clock_to) Is Not Null))
OR (((A.strClock)="1539") AND ((C.SupportEligible)=Yes))
ORDER BY A.Source, A.EmployeeName;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As always Jim, you da man! I didn't know which method, Distinct or Distinct Row or subqueries were more preferable than the other.
Thanks a lot!