Results 1 to 8 of 8

Thread: Delete Duplicate Records from Access Table

  1. #1
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14

    Delete Duplicate Records from Access Table

    '============In Access it requires 4 steps =========================================
    If I have to remove Duplicate records from Table1

    Table1 with column Names(let say Name and Age)

    Needs a Temp Table Table2 with same No. columns, different Names( let say Name1,Age1)

    1: Clear Temp Table
    Code:
    Delete * from Table2
    2: Find First Records of duplicate records and insert these into temp Table2
    Code:
    Insert into Table2 Select  First(Name) as Name1, First(Age)  as Age1 FROM Table1 GROUP BY Name HAVING 
    Count(Name)>1
    3: Delete all duplicate Records from Main Table Table1

    Code:
    Delete * from Table1 where Name in (Select  First(Name) as Name1 FROM Table1 GROUP BY Name HAVING 
    Count(Name)>1)
    4: Insert all Records of Temp Table into Main Table

    Code:
    Insert into Table1 select Name1 as Name,age1 as Age from Table2
    If anyone has better way to delete please post your answer.

    Thanks
    Rahul

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Code:
    SELECT Name,Age INTO [Employee1] FROM [Employee] GROUP BY Name, Age
    DROP TABLE [Employee]
    SELECT Name,Age INTO [Employee] FROM [Employee1]
    DROP TABLE [Employee1]
    Last edited by littleiitin; 11-20-2011 at 11:10 AM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Good one,

    I did not think about this.

    But isn't take much time in dropping and inserting again inserting and dropping if table has huge data, however duplicates are less?


    Thanks
    Rahul
    Last edited by littleiitin; 07-04-2016 at 09:20 AM.

  4. #4
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10
    Rahul,

    1. DROP is a DDL command and DELETE is a DML command
    2. DROP is faster than DELETE

    Explanation: The delete command first stores the data that is going to be deleted. In a more advanced term, it is called a Rollback TableSpace. So this storage event first happens, and only then the rows are deleted. In the case of DROP, there is no storage, and all details and structure of the table is simply erased (removed, so to speak). Since there is no intermediate action, a DROP command is always faster than a DELETE command.

  5. #5
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Thanks Sam for so detailed information...

  6. #6
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10
    Sure. Here's a link which can give more info on DML and DDL (some of them are relevant for MS-Access, although the site is mostly for Oracle)

    What is DML and DDL

  7. #7
    Junior Member
    Join Date
    May 2012
    Posts
    2
    Rep Power
    0

    Delete Double Entry From Access DBF Table

    Suppose we have Table1 as:

    ID Name Address
    1 col1 asdfhj
    2 col2 dfskj
    3 col3 dfgksdlkfg
    4 col1 dfgsjk

    We need to Delete duplicate records from this table where name should be unique then use this query

    select * into Check from Table1 where name in
    (
    Select name from
    (
    SELECT Table1.Name, Count(Table1.Name) AS CountOfName
    FROM Table1
    GROUP BY Table1.Name
    ) where countofName=1)

    this will create a check table with unique records.

    Thanks....do reply.....if it was helpful............

  8. #8
    Junior Member
    Join Date
    Oct 2011
    Posts
    5
    Rep Power
    0
    Hi Nishun,

    I used your SQL statement but it gives only records which are not repeating. So in the below example we are only left with col2 and col3 values.

    I am assuming that count function will give 2 for both col1 values hence will not pass them in new table. We should have a way to get atleast one col1 in the new table.

    Thanks
    Sidd
    Last edited by Excel Fox; 03-07-2013 at 11:36 AM. Reason: Removed Quote

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. Excluding Records of one Table from the Other Table
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Deleting Records Using Join
    By MMishra in forum MS-Access Tips And Tricks
    Replies: 0
    Last Post: 04-24-2013, 04:06 PM
  4. Delete Double Entry From Access DBF Table
    By MASIF in forum Access Help
    Replies: 1
    Last Post: 03-07-2013, 11:40 AM
  5. Checking Table Exist in Access Database or Not
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 11-16-2011, 04:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •