Delete Duplicate Values

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ramdil
    New Member
    • Dec 2007
    • 46
    #1

    Delete Duplicate Values

    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
    Tags: None
    • FishVal
      Recognized Expert Specialist
      • Jun 2007
      • 2656
      #2
      Hi, ramdil.

      Take a look at a similar thread Delete Similar Records

        Comment

        • ramdil
          New Member
          • Dec 2007
          • 46
          #3
          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.Pleas e 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]

            Comment

            • FishVal
              Recognized Expert Specialist
              • Jun 2007
              • 2656
              #4
              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

                Comment

                • ramdil
                  New Member
                  • Dec 2007
                  • 46
                  #5
                  Hi

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




                  Originally posted by 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

                    Comment

                    Previous template Next
                    Working...