Delete Duplicate Values

ramdil

Hi All

I have table and it have around 90000 records.Its primary key is autonumber field and it has also have date column and name, then some other columns
Now i have problem with the table,as my table contains duplicate entries for a particular date.How can i delete the duplicate entries from the table for that particular column,Now i am doing manually with name column as it will be unique for that date.Can any one help me giving the query by which if i execute the query the duplicate values for that particular date should be deleted.When i searched google, i found a solution of copying the table and then set the unique column to primary key and write append query so that duplicate values will not be inserted.,I wish if i get a query by which i can delete the duplicate entries.The unique column in my table is name.Thanks in advance

May 27 '08 #1

FishVal

Hi, ramdil.

Take a look at a similar thread Delete Similar Records

May 27 '08 #2

ramdil

Hi

I had went through that link, but its not helping me in getting my result.I will just explain my scenario.My table has three columns,ID which is autonumber,Name which is text and date which is of type date.Now my duplicate data will be like this
100 TestName 23/01/2007
101 TestName 23/01/2007
102 Name2 24/01/2007
103 Name3 23/01/2007 and so on

Now i want a select statment which will fetch me only first two records ie 100 and 101 as you can see that name and date col are same and so is duplicate.Please help me in this as i am stuck on this.Thanks in advance


QUOTE=FishVal]Hi, ramdil.

Take a look at a similar thread Delete Similar Records[/quote]

May 27 '08 #3

FishVal

Hi, ramdil.

If

• that is one-time action to cleanup the table
• you mention to delete [Date] and [Text] duplicates leaving only those having maximal [Number] field

, then the simplest way is to create a query grouping records by [Date] and [Text] with aggregating function - Max([Number]), and use the query to make new table.
Then you may delete the old one and compact/repair db.

Regards,
Fish

May 27 '08 #4

ramdil

Hi

Thanks for the information.I think it is the best idea

Hi, ramdil.

If

• that is one-time action to cleanup the table
• you mention to delete [Date] and [Text] duplicates leaving only those having maximal [Number] field

, then the simplest way is to create a query grouping records by [Date] and [Text] with aggregating function - Max([Number]), and use the query to make new table.
Then you may delete the old one and compact/repair db.

Regards,
Fish

May 28 '08 #5