Finding duplicate records based on two fields in same table

[tyriekfv]

I have a table that has about 40 fields and over 8000 records. I need to find and delete all duplicates based on two fields in that table.

ID Year Field.. Field... So on
200 2060
200 2060
200 2061
300 2061
300 2061

Seems easy enough to do with 1 field but soon as you add the second...bleh. There is no unique key as the ID field can be in the table more than once for multiple years, but the same ID and same year can only be in the table once. Please help.

Thanks much,

Messiah

[Dal Jeanis]

So there is no primary key in the table? It's not difficult to delete duplicate records, but knowing which one to delete with a delete query is important. Here's some SQL to find your duplicate problems:

SELECT t1.ID, t1.Year, Count(*) 
FROM MyTable as t1
GROUP BY t1.ID, t1.Year
HAVING Count(*) > 1;

That will find you all the particular cases in which you have duplicates. You'll have to replace the MyTable name and the field names with your real field names. By the way, "Year" is not a good choice for a field name, if that is really one of your field names. You should avoid reserved words and function names.

Hmmm.

Okay, Here's the easiest way I can think of.

First, create a play copy of your database.

Second, copy that one table, structure and data, to a backup name, still in the play database.

Third, delete all the data in the real (play) table.

Fourth, in design mode, establish a multi-column unique key on the fields that cannot be duplicated in that real (play) table.
http://stackoverflow.com/questions/2...cess-databases
http://bytes.com/topic/access/answer...que-constraint

Fifth, create an insert query to add the data back from your backup. The records which are dups will not be added back.

If that works, then you can do the same process on a work copy of your real database, after backing it up someplace safe.

[tyriekfv]

So there is no primary key in the table? It's not difficult to delete duplicate records, but knowing which one to delete with a delete query is important. Here's some SQL to find your duplicate problems:

SELECT t1.ID, t1.Year, Count(*) 
FROM MyTable as t1
GROUP BY t1.ID, t1.Year
HAVING Count(*) > 1;

That will find you all the particular cases in which you have duplicates. You'll have to replace the MyTable name and the field names with your real field names. By the way, "Year" is not a good choice for a field name, if that is really one of your field names. You should avoid reserved words and function names.

Hmmm.

Okay, Here's the easiest way I can think of.

First, create a play copy of your database.

Second, copy that one table, structure and data, to a backup name, still in the play database.

Third, delete all the data in the real (play) table.

Fourth, in design mode, establish a multi-column unique key on the fields that cannot be duplicated in that real (play) table.
http://stackoverflow.com/questions/2...cess-databases
http://bytes.com/topic/access/answer...que-constraint

Fifth, create an insert query to add the data back from your backup. The records which are dups will not be added back.

If that works, then you can do the same process on a work copy of your real database, after backing it up someplace safe.

Thanks I will give this a shot. And year is a field spit out by the game files that we use to update the site. Hell there is a field name "h" and a bunch of other short ones like that. Not very good practice I know but it is what it is. I really appreciate your help, it will save me a tremendous amount of time. Gonna go plug this in now.

[tyriekfv]

It worked. I copied the table, structure only, created the primary key based on the fields thanks to your link. I then copied and appended the old table into the newly created table and done. Thanks so much!

Messiah

[Dal Jeanis]

YOu're welcome. Please mark the thread solved. Top of page, under "Thread Tools".