Form showing employees twice for some supervisors

[alkahl]

I have a form that shows a listing of employees for a supervisor and the employees are listed twice. Nothing has changed in the Access Database or the Query that fills in the list and it does not do this for every supervisor listed. Anyone have any ideas on what I can do to remove the duplicate listing of the employee? The employee is only listed in the database once and the unique identifier is listed as yes in the query. I can’t seem to find on the form why it would list the employees twice for some of the supervisors. Any ideas would be great.

[aytee111]

The problem is not on the form, it is in the record source. Fix that in query design - you must be joining in a table/query that has the employee listed more than once.

[alkahl]

Thanks, I have checked the query and there is no duplication. This only happens to a few supervisors not all of them. Any other ideas I should check out?

[andy49]

can you send an image of the problem. Maybe an image of the query. We'll be able to get a better idea then

[aytee111]

I have checked the query and there is no duplication

That is not possible - the form is only a display of the underlying query. With the form open showing duplicates, reproduce the record source in query design. They will match exactly every time, no exceptions.

[alkahl]

Here is the Query I am running.

SELECT DISTINCT Employee.ID, Employee.LANID, Employee.EmpRefID, Employee.Admin, IIf(IsNull([isSupervisor].[SupervisorRefID]),"No","Yes") AS isSupervisor, Employee.SupervisorRefID, Employee_NameInfo.FullName AS EmpFullName, Supervisor.ID, Supervisor.LANID, Supervisor.EmpRefID, Supervisor.Admin, Supervisor.SupervisorRefID, Supervisor_NameInfo.FullName AS SupFullName, Manager.ID, Manager.LANID, Manager.EmpRefID, Manager.Admin, Manager.SupervisorRefID, Manager_NameInfo.FullName AS ManagerFullName, Manager_1.ID, Manager_1.LANID, Manager_1.EmpRefID, Manager_1.Admin, Manager_1.SupervisorRefID, Manager_1_NameInfo.FullName AS Manager_1_FullName, Manager_2.ID, Manager_2.LANID, Manager_2.EmpRefID, Manager_2.Admin, Manager_2.SupervisorRefID, Manager_2_NameInfo.FullName AS Manager_2_FullName, Manager_3.ID, Manager_3.LANID, Manager_3.EmpRefID, Manager_3.Admin, Manager_3.SupervisorRefID, Manager_3_NameInfo.FullName AS Manager_3_FullName, Manager_4.ID, Manager_4.LANID, Manager_4.EmpRefID, Manager_4.Admin, Manager_4.SupervisorRefID, Manager_4_NameInfo.FullName AS Manager_4_FullName, Manager_5.ID, Manager_5.LANID, Manager_5.EmpRefID, Manager_5.Admin, Manager_5.SupervisorRefID, Manager_5_NameInfo.FullName AS Manager_5_FullName, Manager_6.ID, Manager_6.LANID, Manager_6.EmpRefID, Manager_6.Admin, Manager_6.SupervisorRefID, Manager_6_NameInfo.FullName AS Manager_6_FullName
FROM (((((((((((((((((tbl_Supervisor_Employee AS Employee LEFT JOIN tbl_Supervisor_Employee AS Supervisor ON Employee.SupervisorRefID = Supervisor.EmpRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager ON Supervisor.SupervisorRefID = Manager.EmpRefID) LEFT JOIN EmployeeInformationTable AS Employee_NameInfo ON Employee.EmpRefID = Employee_NameInfo.EmployeeRefID) LEFT JOIN EmployeeInformationTable AS Supervisor_NameInfo ON Supervisor.SupervisorRefID = Supervisor_NameInfo.EmployeeRefID) LEFT JOIN EmployeeInformationTable AS Manager_NameInfo ON Manager.SupervisorRefID = Manager_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_1 ON Manager.SupervisorRefID = Manager_1.EmpRefID) LEFT JOIN EmployeeInformationTable AS Manager_1_NameInfo ON Manager_1.SupervisorRefID = Manager_1_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_2 ON Manager_1.SupervisorRefID = Manager_2.EmpRefID) LEFT JOIN EmployeeInformationTable AS Manager_2_NameInfo ON Manager_2.SupervisorRefID = Manager_2_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_3 ON Manager_2.SupervisorRefID = Manager_3.EmpRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_4 ON Manager_3.SupervisorRefID = Manager_4.EmpRefID) LEFT JOIN EmployeeInformationTable AS Manager_3_NameInfo ON Manager_3.SupervisorRefID = Manager_3_NameInfo.EmployeeRefID) LEFT JOIN EmployeeInformationTable AS Manager_4_NameInfo ON Manager_4.SupervisorRefID = Manager_4_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_5 ON Manager_4.SupervisorRefID = Manager_5.EmpRefID) LEFT JOIN EmployeeInformationTable AS Manager_5_NameInfo ON Manager_5.SupervisorRefID = Manager_5_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS Manager_6 ON Manager_5.SupervisorRefID = Manager_6.EmpRefID) LEFT JOIN EmployeeInformationTable AS Manager_6_NameInfo ON Manager_6.SupervisorRefID = Manager_6_NameInfo.EmployeeRefID) LEFT JOIN tbl_Supervisor_Employee AS isSupervisor ON Employee.EmpRefID = isSupervisor.SupervisorRefID
WHERE (((Employee.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Supervisor.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_1.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_2.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_3.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_4.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_5.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor])) OR (((Manager_6.SupervisorRefID)=[Forms]![frm_Admin]![cmboSupervisor]))
ORDER BY Employee_NameInfo.FullName;

[aytee111]

Copy and paste it into a new query design. Open the form, select the supervisor that displays duplicates and run the query. If both sets of data are not exactly the same then this is not the correct record source.

[alkahl]

Thanks aytee111,

They match completely. Same records are displayed. For one supervisor I see employees that show up doubled in the query and for another supervisor they do not show up doubled.

But the new Query and the old Query data match for what ever supervisor I run through it.

[aytee111]

Now get the query working correctly, trace which table holds the duplicate employee records and go from there. The SQL above is way too complicated, I'm not sure how you are going to find the problem. And all those left joins! Make multiple simple queries, at least for troubleshooting, one table at a time, and that way you will be able to immediately see when the duplicates start appearing.

[alkahl]

Thanks aytee111, I am working at it now.