A simple Query...or so I thought!!

[Deleted member 73419]

Hi,

I have inherited a database with a rather large table (12K+ records) which contains a lot of duplicates.

What I need help with is a query to delete any duplicates having a count > 1 and leaving me with just one copy.

The database looks like this:

ID          Group          Title
==          ====            ====
1           000            Blood
2           001            Sweat
3           002            and Tears
4           000            Blood
5           001            Sweat
6           002            and Tears

Any help is appreciated

Thanks

 

[Galaxiom]

Do a Group By on the Group and Title fields with a Min on the ID field.
Delete any records by ID not included in this result.

Incidentally 12K records isn't large.
I have a table with 4.6 million records and no sign of Access choking on it yet.

 

[Deleted member 73419]

Do a Group By on the Group and Title fields with a Min on the ID field.
Delete any records by ID not included in this result.

Incidentally 12K records isn't large.
I have a table with 4.6 million records and no sign of Access choking on it yet.

4.6 million records Ok i'll re-phrase that...not the amount of records i'd like to go through manually!!

I'll give your suggestion a go and see how I get on

Thanks

 

[chundusmgs]

4.6 million records Ok i'll re-phrase that...not the amount of records i'd like to go through manually!!

I'll give your suggestion a go and see how I get on

Thanks

Do you want to delete duplicate record or duplicate field. Duplicate record means all fields have the same value. Deleting dupicate field use group by option. Deleting duplicate records visit learnmsaccess.weebly.com. You will find the answer

 

[Galaxiom]

I gathered the goal was to delete records that duplicate information in the Group and Title fields but have different IDs. ID is generally a primary key so there would be no duplicate records in the precise sense of the word.