elete the the duplicates keeping one data from the duplicates

[akashneelh]

Hi,

I have a data as attached below, I want to delete the the duplicates keeping one data from the duplicates value.Is is possible using qury criteria in MSaccess query.

Please help Guys

ORIGINAL DATA REQUIRED DATA abc abc abc ccc ccc aaa ccc ddd ccc
aaa
ddd
ddd
ddd
ddd

 

[Alansidman]

Here is a method to remove duplicates.

http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

 

[akashneelh]

Hi,

This process is fantastic.But it is a manual method .I want to implement this process in a macro.It is possible to make a copy of the table and set primary key in the Macro...Please reply....

 

[rodmc]

use excel 2007 or above and go to the Data>Remove duplicates

 

[Alansidman]

A VBA procedure could be written to mimic exactly what Mike has done in the video, but one would need to see the current table with sample data to ensure that it works properly. It would entail many lines of code but is possible. Unless this process is to be repeated on a regular basis, ie. several times a day or once a day, I would stay with the manual process. It only takes 2-3 minutes to do. If you are eliminating dupes from the same table everytime, you could build a template table and copy it each time you needed to do this.

 

[akashneelh]

I have succeeded to make the macro procedure....

Here: Table1 contains duplicate values in mobileno field.

Firstly I created a New table with a primary key..

Query 1. [FONT=&quot]Create table Table2
(
mobileno double primary key,
status text
)[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Query2.[/FONT]
[FONT=&quot]INSERT INTO Table2
SELECT Table1.*
FROM Table1;[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Now the new table is the required output.
[/FONT]