Question Delete Duplicates in table when two or more fields are the same

[Steve_T]

Hello,
I have looked and looked at all the delete statements on the forum but i am not getting anywhere fast.
I have a table called "tblMaster"
With the following fields that i want to search by

IDNo
DateRequired
DateBooked

In short i want to search the table and delete all duplicates where "IDNo" and "DateRequired" both match.
EG

PS "IDNo" will repeat many times as its a Users ID number so i cant set it to not allow duplicates in the table's design.


IDNo --------------DateRequired --------------DateBooked

32 -----------------04/08/08 -------------------05/08/2008 15:09:00
32 -----------------04/08/08 -------------------05/08/2008 14:00:00

I am looking to delete all duplicates so that all that i left is the one with the first "DateBooked" Time stamp. The delete statement will need to delete the whole entry as there are other fields in it that i have not mentioned above

I have tried to create a delete statement using count >1 for both "IDNo" and "DateRequired" but the query will not run. "Cannot Group on fields marked with *"

HELP!!!

 

[Alansidman]

Maybe this will be helpful http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

Alan

 

[Steve_T]

HI,
Thanks but that does not do anything.

 

[georgedwilkinson]

This certainly is going to be difficult without the primary key. Does the table have a unique key or primary key? Could you supply us with that/those column names?

 

[Mike375]

"I am looking to delete all duplicates so that all that i left is the one with the first "DateBooked" Time stamp. The delete statement will need to delete the whole entry as there are other fields in it that i have not mentioned above"

I am not quite clear as to whether you want to remove the entire duplicate record or just the entries in the two fields.

If the entire record then I would make a query and add a field

NewField:[IDNo] & "" & [DateRequired]

Do a MakeTable query. Then make a copy of the table and set NewField for no duplicates and then append query.

If the requirement is to just make the fields null where the duplicates are found then I would do the above first. Then on the table where the duplicates were removed I would make a form and also a form on the table you made where :[IDNo] & "" & [DateRequired] was done. On the form based on the table where the duplicate rows have been removed I would make a macro or code that opened the form on the table that contained all the records and on the basis of NewField. The macro or code would open the related form and set the value of the two fields in question to null (goToNextRecord) as there will be more than one. The macro or code would then move to the next record and repeat the process. Somewhere here I think I have that already made except it was for inserting a number, but same deal.