I've found duplicates but how do I delete?

[rocktap]

Apologies in advance for what is probably a newbie error:



I'm using MS Access 2013 Find Duplicates Wizard. I successfully find my duplicates but the results display with an auto-generated field called 'Number of Dups'. How do go on to actually delete the duplicates from here? All the tutorials I've found indicate that I should be getting a list of the duplicates where I can just highlight and delete the dups. But this wizard is summarising them and not showing me the individual records.

Regards

Peter

[June7]

What tutorial? Here is one http://www.techrepublic.com/article/...-access-query/

Some record edits will have to be accomplished manually.

[rocktap]

Yes I viewed that tutorial - but my wizard (Access 2013) returns results in this format:



Basically it lists the records that have duplicates and creates a new field that indicates the number of dups. I'm happy to manually edit the records but I can't view them individually

[June7]

I don't know why your wizard results in an aggregate query. I just built a Find Duplicates with the wizard according to the tutorial and it lists the individual records. Here is the sql statement.

SELECT DistrictInfo.[DistrictManager], DistrictInfo.[DistrictID], DistrictInfo.[RegionName]
FROM DistrictInfo
WHERE (((DistrictInfo.[DistrictManager]) In (SELECT [DistrictManager] FROM [DistrictInfo] As Tmp GROUP BY [DistrictManager] HAVING Count(*)>1 )))
ORDER BY DistrictInfo.[DistrictManager];

You can build query without the wizard. Use query designer or SQL view and type the statement.

[rocktap]

Thanks June7

I will have a go at the sql but I'm still learning that side of things (alternatively I might reinstall Access 2010!

Thank you for your help :-)

[June7]

One of the steps in the wizard is to select other fields (than the one(s) used to look for dups) in the query. Did you do that? Note the aggregate nested subquery in the example.

[rocktap]

Ah yes! Problem solved! I was selecting two fields in the first step but I did as you suggest and now I get proper, individual records. Thank you so much :-)