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

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]

---

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

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.

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

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

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

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