MS Acces queries or using vba

[Saisai]

Kindly help me in the data, I am having a access table that has EMP ID and Name. I want to delete duplicates, BUT not same name...ie, if the ID is assigned to more than one person then we should delete it. If it is same person, we have to retain it. u

Output should be only 1003 Vas, because, 1001 is assigned to jack, MAC and John, If 1001 assigned only to Jack, if should be retained, since it is assinged to MAC and JOHN we are deleting it.. kindly help
ie ,
EMP ID Name


1001 Jack
1001 Jack
1001 MAc
1003 Vas
1001 John

Output
EMP ID Name
1003 Vas

[June7]

Try:

DELETE FROM tablename WHERE EmpID IN (SELECT EmpID FROM tablename GROUP BY EmpID HAVING Count(*)>1)

[Saisai]

Thank you. But it also deletes the 2nd record of a person which is not assigned to others.. That is if 1001 is not assigned to other person, it should not delete it. 1 person can have multiple records (1001 Sam, 1001 sam) with same name and ID.. When name changes but id is same then delete... That is if 1001 is also assigned to John, then it should delete all record of John and sam

[June7]

Okay, try this:

DELETE FROM Table1 
WHERE EmpID 
IN (SELECT DistinctIDNAME.EmpID 
      FROM (SELECT DISTINCT Table1.EmpID, Table1.EmpName FROM Table1) AS DistinctIDNAME
      GROUP BY DistinctIDNAME.EmpID
      HAVING Count(EmpName)>1);

I added two records 1002 Joan and after running query, the 2 Joan and 1 Vas records remain.

[Saisai]

Thank you it worked well

[orange]

Perhaps you should adjust your logic to prevent this to occur
".....if the ID is assigned to more than one person then we should delete it."

Why input garbage, then run a separate process to clean things up??

[Saisai]

Actually it a job databse where one people work on several ids.. And one more thing.. The query takes 10 mins to. Clean up.. Any suggestions to run it quickee

[isladogs]

See my article on optimising queries Speed Comparison Tests 8 (isladogs.co.uk)