Finding Duplicate Fields between two fields

[Navo720]

I would like some help in finding duplicate entries between two different fields. I have an Date field, EmployeeID field, a few other fields, followed by an AssignedEmployeeID field. There is a possibility that someone could mistakenly enter the same ID in both the EmployeeID and AssignedEmployeeID fields for different records on the same date. I want to be able to find the duplicates, but I've only been able to view duplicates on either the EmployeeId OR the AssignedEmployeeID. Again, the EmployeeID might be on record 1 while the AssignedEmployeeID might be on record 100. Thanks in advance for your help!!

 

[hneal98]

Put a piece of code in the Before Update event of the AssignedEmployeeID that will check to see if the data entered is the same as that in the text property of the EmployeeID.

 

[PHV]

the same ID in both the EmployeeID and AssignedEmployeeID fields for different records on the same date
You may try this query to find the duplicates:
SELECT A.EmployeeID, A.Date, A.AssignedEmployeeID, B.EmployeeID, B.AssignedEmployeeID
FROM yourTable A INNER JOIN yourTable B
ON (A.AssignedEmployeeID=B.EmployeeID) AND (A.Date=B.Date)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hneal98]

PHV's suggestion is great for find duplicates that are already there, but my suggestion is to catch potential dupes before you put them in the database.

 

[Navo720]

First, thank you guys for the response. Please forgive my ignorance, I am very new to programming. The probable duplicates are in same single table, so I not sure how to do the query that was suggested. I also do want to allow the duplicates (sometimes they are necessary), but I want to be able to view them and then decide if it needs to be removed or not. There are sometimes so many records that it's hard to see the duplicates. Thanks again.

 

[PHV]

I not sure how to do the query that was suggested
Create a new query, choose the SQL pane, paste the posted sql code, choose the data view pane.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244