delete duplicates from table (1 Viewer)

[cpampas]

Hi there,

I would appreciate your advice on the right way to do this :
i have a recordset, of wich I would like to delete the records where the field horas and the field ano is repeated in one or more records

maqID	ano	horas
1	2002	6300
2	2003	6300
3	2002	6400
4	2015	2530
5	2014	8444
6	2009	1630
7	2003	8223
8	2009	1630

in this case i would like to delete the records with maqID 6 and 7
what is the best way to achieve this ? the recordset is based on a recordsetclone of my current subform
thanks for your help

 

[theDBguy]

maqID 7 doesn't look like a duplicate to me.

 

[isladogs]

There are several methods.
1. Create a Find duplicates query wizard selecting both fields. If you only gave a few duplicates, just delete one of each manually.
2. Create a duplicates query as above. Now create a second query to find the first record in each set of duplicates...these are the records you will keep. Now join the dupes query to the second query using an unmatched query. Delete all records it displays
3. Or if you don't care which duplicate record is deleted, create a new table with the same structure but create a unique index on these two fields to prevent duplicates.. Now try to import all records into the new table. Only one record will be imported where dupes exist.

 

[cpampas]

theDGguy, sorry , you are right i meant 6 and 8

 

[cpampas]

isladogs, i will give it a try, and let you know how it went
thanks

 

[isladogs]

If you want to delete all records which are duplicates, just run the duplicates query wizard then delete them all.

 

[theDBguy]

theDGguy, sorry , you are right i meant 6 and 8

In that case, maybe you could try something like this (make sure you have a backup copy first).

DELETE FROM TableName WHERE maqID IN (SELECT maqID FROM TableName GROUP BY maqID HAVING Count(*)>1)

(untested)
Hope that helps...

 

[cpampas]

DELETE FROM TableName WHERE maqID IN (SELECT maqID FROM TableName GROUP BY maqID HAVING Count(*)>1)

i was looking for a way to delete the records where the field horas and the field ano were equal, and not where the field maqID repeats, so I tried this that didnt work :

DELETE FROM TableName WHERE maqID IN (SELECT maqID FROM TableName GROUP BY ([horas] & [ano]) HAVING Count(*)>1)

I also tried isladogs method number , and was able to create the querys, but on the last one i cant delete records, getting a message that data can not be deleted because is read only. in the last query i joined the dup query, with the first record query by the field maqID
any Thoughts ?

 

[isladogs]

Not sure which of my methods you meant.
When the delete query is based on more than one table, you need to ensure unique records = Yes in the query property sheet.
In SQL view, that's equivalent to DELETE DISTINCTROW

 

[theDBguy]

i was looking for a way to delete the records where the field horas and the field ano were equal, and not where the field maqID repeats, so I tried this that didnt work :

DELETE FROM TableName WHERE maqID IN (SELECT maqID FROM TableName GROUP BY ([horas] & [ano]) HAVING Count(*)>1)

Sorry, it's hard to compose something without any data. That's why I posted (untested) with my suggestion. Maybe you could try it this way then (still untested).

DELETE FROM TableName WHERE "" & horas & ano IN 
    (SELECT "" & horas & ano FROM TableName GROUP BY "" & horas & ano HAVING Count(*)>1)

 

[arnelgp]

backup your table first before anything.

DELETE *
FROM yourTable
WHERE maqID <>  (SELECT Min(maqID) AS MinOfID FROM yourTable AS Dupe    
   WHERE (Dupe.ano = yourTable.ano)                        
   AND (Dupe.horas = yourTable.horas));

 

[cpampas]

Thank you all for your help
I ended up using arnelgp approach wich works great

 

[vhung]

Thank you all for your help
I ended up using arnelgp approach wich works great

Good day to you cpampas

Allow me to join this discussion though it is too late.

Please see the attachment if it will work for you or not.