AccessAddict
Technical User
Hi
I've just looked at a db built by a family friend, and discovered many duplicate entries in a table (amongst other things lol). I've created a Find Duplicate query and then a delete query based on the Find Duplicate query, but that will obviously delete ALL occurences of the duplicate data when I need to retain just ONE instance of the record. The table contains a lot of records so going through it manually would be quite a task.
If the Find Duplicate query finds eg ID no's 34, 451, 934 as records containing the duplicate data, how can I delete ONLY record numbers 34 and 451 leaving 934 intact?
The queries at the moment are
And
Of course, fixing the problem as to how the duplicates got there in the first place is another story - or headache lol.
Thanks for your time
Cheers
Alan
I've just looked at a db built by a family friend, and discovered many duplicate entries in a table (amongst other things lol). I've created a Find Duplicate query and then a delete query based on the Find Duplicate query, but that will obviously delete ALL occurences of the duplicate data when I need to retain just ONE instance of the record. The table contains a lot of records so going through it manually would be quite a task.
If the Find Duplicate query finds eg ID no's 34, 451, 934 as records containing the duplicate data, how can I delete ONLY record numbers 34 and 451 leaving 934 intact?
The queries at the moment are
Code:
SELECT tblMailShot.Surname, tblMailShot.HouseNo, tblMailShot.Postcode, tblMailShot.MailShotID
FROM tblMailShot
WHERE (((tblMailShot.Surname) In (SELECT [Surname] FROM [tblMailShot] As Tmp GROUP BY [Surname],[HouseNo],[Postcode] HAVING Count(*)>1 And [HouseNo] = [tblMailShot].[HouseNo] And [Postcode] = [tblMailShot].[Postcode])))
ORDER BY tblMailShot.Surname, tblMailShot.HouseNo, tblMailShot.Postcode;
And
Code:
DELETE qryFindDupForMailShot.MailShotID
FROM qryFindDupForMailShot;
Of course, fixing the problem as to how the duplicates got there in the first place is another story - or headache lol.
Thanks for your time
Cheers
Alan