By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Delete Duplicate Values

P: 46
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
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, ramdil.

Take a look at a similar thread Delete Similar Records
May 27 '08 #2

P: 46
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
Expert 2.5K+
P: 2,653
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

P: 46
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

Post your reply

Sign in to post your reply or Sign up for a free account.