Deleting Duplicates

[Guy Winfield]

Hello everyone,

I have hundreds of duplicates in my database and i've tried using logic to delete them but there are still thousands left over. I know duplicates can be removed by using VBA, however i have no idea how to use VBA IDE. Help and suggestions ?

Thank you !

[ranman256]

Can you make a duplicate table with a keyed field?
Then append the data to it, thus eliminating the dupes.

[Guy Winfield]

Well the problem is that i also have duplicates in a query

[ItsMe]

It's been a while but this article seems to describe the process.
https://support.microsoft.com/en-us/kb/109329

Here it is in a video
https://www.youtube.com/watch?v=e-zKboe6ZEw
Edit: I had the wrong link earlier.

[John_G]

Well the problem is that i also have duplicates in a query

I'm not sure what you mean by this - queries don't have duplicates - only the tables that are used in the query do.

But what do you mean by "duplicates" in your case? Are entire records duplicated, or just parts of them?

If entire records are duplicated, it's an easy fix - make a totals query, use and all the fields in the group by. Once you have that working, change the from a Select query into a Make-table query to save the results.

[Guy Winfield]

Sorry if i'm expressing myself badly, what i meant by having duplicates in my queries is that. Seeing as i know that there are duplicates my queries tend to create more duplicates (to a certain extent, i do use some criteria to reduce the amount that gets through).

The duplicates are parts of duplicated records, so the record would be the exact same however a couple of numbers would change in one or 2 fields.

[orange]

Why do you have duplicates? Do you have a Primary Key on the table?
Data resides in tables, not queries per se as John said.

It sounds to me that you have a data structure issue. Can you tell us more about your database and application?

[Guy Winfield]

I have duplicates for 2 reasons:
-First of all the data that i am managing has duplicates, these tables aren't controlled by me though, i simply import and use them.
-I had to use a "create table" query in order for some of my work colleagues to manually fill in some fields newly created fields. Create table--> copy structure--add new fields with new data manually -->crush previous table to make new table with same data but with added fields manually.

This new table is then put in a query and that is where the main source of duplicates come from. Seeing as i've had to re-use tables from a previous query in this query because i have a few calculated fields that needed to be added and is impossible to be done prior to the creation of the new table. (hope i'm making sense). I guess this is my structure issue, but it is unavoidable unfortunately. Because i can't use my new calculated fields without the manual data.

So this means that i have a final query with more than 2000 duplicates instead of the previous 169 (impressive i know). Whats your take ?

So the ideal situation would be to dispose of the duplicates in this final phase !

[orange]

I would think the more ideal situation would be to remove all duplicates before doing the real processing, if possible.

In a scenario such as importing 1000000 records where 300000 are duplicates. Reduce the working set to 700000 before subsequent processing would reduce overall processing.

The table you import into should have a defined structure with a primary key. Based on your experience if there is some combination of fields that make a record unique, you could add a unique composite index of those fields before importing. When you do the import, the records failing the composite unique index will be dropped by the database software and not imported.