removing duplicates issues

[faxof]

hi
i have three tables "YE", "PYE" and "CN"

they have duplicates in them

i got a button to extract their duplicates to a speadsheet

i need the button to also remove the duplicates from the tables AND keep a copy of the tables in their original (with duplicates) format

i.e. YE, PYE and CN will have no duplicates, but their copies (YEcopy, PYEcopy and CNcopy) will have the duplicates
i tried using append, it seemed to work when copying the table structure manually, but i need it to do it all by code or macros

i think i've broken my append query now - it copies all records not just the non duplicates

appreciate it if anyone can help

rgds
Faxof

 

[SoloDroid]

Hello Faxof,
Assuming that you had a table named "Dups" that looked like this:

MyID    MyField
----    -------
1       AA
2       BB
3       BB
4       CC
5       DD
6       EE
7       EE
8       FF
9       GG
10      HH
11      HH
12      HH
13      II
14      JJ
15      KK
16      LL
17      LL

The following code would accomplish the desired task:

Sub Main()
    CurrentProject.Connection.Execute "SELECT Dups.* INTO CopyDups FROM Dups"
    CurrentProject.Connection.Execute "DELETE * From Dups WHERE MyID NOT IN (SELECT FIRST(MyID) FROM Dups GROUP BY MyField)"
End Sub

The result:

MyID    MyField
----    -------
1       AA
2       BB
4       CC
5       DD
6       EE
8       FF
9       GG
10      HH
13      II
14      JJ
15      KK
16      LL

Plus a copy of the original table, named CopyDups.
Enjoy, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°

 

[jdegeorge]

Robert

This sounds like interesting code, but what version of Access are you using? And, how is this run, as code behind the button's ON CLICK event?

Thanks!

Jim "Get it right the first time, that's the main thing..."

 

[SoloDroid]

I wrote that in Access 2002. The code could be placed in a button's On Click event. The above was simply placed in a standard VBA module's Main() sub procedure.

If you have an older version of Access, say 97, the code won't run without modifications. Access 97 doesn't support subqueries -- as far as I remember. You would have to create two queries and reference the first inside of the second.

If your version of Access doesn't support the CurrentProject object, you will need to create an ADODB.Connection object to replace CurrentProject.Connection. I do not remember if Access 2000 has the CurrentProject object.
Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°

 

[jdegeorge]

Robert:

We'll be upgrading to 2000 shortly, so I'll save this information for later!

Thanks!

Jim "Get it right the first time, that's the main thing..."