Delete Duplicate Records from Access Table

[littleiitin]

'============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


Delete * from Table2

2: Find First Records of duplicate records and insert these into temp Table2


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



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



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

[Excel Fox]

SELECT Name,Age INTO [Employee1] FROM [Employee] GROUP BY Name, Age
DROP TABLE [Employee]
SELECT Name,Age INTO [Employee] FROM [Employee1]
DROP TABLE [Employee1]

[littleiitin]

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

[S M C]

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.

[littleiitin]

Thanks Sam for so detailed information...
:cool:

[S M C]

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 (http://orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_co mmands)

[nishunksaxena]

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............

[siddharthsindhwani]

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