duplicate problems 1

[faxof]

i have table called "YE"
two fields; reference (int) and data (string)
there is no primary key
i need to make a copy of the table called "YEorig" and remove the duplicates from "YE"

To make a copy of the table i used this code:

 CurrentProject.Connection.Execute "SELECT YE.* INTO YEorig FROM YETest"

then i made a delete query to empty the "YE" table

then i used an append query to copy the non duplicates from "YEorig" to "YE"

here's the problem. . .

it appends all the records coz there is no primary key, how can i (using code when i create the table) make reference the primary key in the "YEorig" table?

NB: i must not make a primary key in the "YE" table

can you help with this one eh?

thanx Faxof

 

[Norris68]

SELECT DISTINCT YE.* ...

 

[faxof]

hi - thanx for the suggstion
i tried that but it didn;t make any different - it created the table just the same but the table still has no primary key

any ideas?

quizzed Faxof

 

[SoloDroid]

faxof,
Why didn't you reply to your first thread, which I took the time to answer? You used the code that I posted [as I see it above], but you didn't reply.

All you need to do is add an AutoNumber field to your table and voila, you will have a primary key with unique values for each field. Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[faxof]

hi robert

i did use your code (not sure why i created a new thread - sorry)
but anyway

here's what you suggested:

[red]
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.
[/red]


the line [red]

 CurrentProject.Connection.Execute "SELECT Dups.* INTO CopyDups FROM Dups"

[/red] worked to copy make a copy of the table.

in your example the duplicated data is the MyField field; in my table the duplicated data would be the MyID field.
so. . .
i tried to edit the line [red]

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

[/red]
by changing it to

  CurrentProject.Connection.Execute "DELETE * From Dups WHERE MyID NOT IN (SELECT FIRST(MyID) FROM Dups GROUP BY MyID)"

that didn;t work so i just used the first line to create the table and use an append query to append all the non dups: but now i have the problem of there being no primary key in the target append table.

any ideas now?

really quizzed Faxof

 

[SoloDroid]

Hello Faxof,
You are correct, the code will not work if you do not have a primary key named "MyID". Without a primary key or some unique column in the table, it is rather difficult to remove duplicates with a query alone. As I suggested above, the easiest solution would be to add a field called "MyID" of type AutoNumber. Access will then assign each row a unique number, then the code will work. It is not recommended to have a table without a primary key, however if you absolutely cannot add a primary key for some reason, I will see if I can throw something together for you.
Good Luck, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[SoloDroid]

Hello again,
This code should do the trick:

Sub Main()
    Dim strLast As String, objRSet As New ADODB.Recordset

    strLast = vbNullString

    With objRSet
        .Open "Dups1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

        While Not .EOF
            If strLast <> !MyField Then
                strLast = !MyField
            Else
                .Delete adAffectCurrent
                .Update
            End If

            .MoveNext
        Wend

        .Close
    End With

    Set objRSet = Nothing
End Sub

Enjoy, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[SoloDroid]

Oh yah, just replace

Dups1

with the name of the table. Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[SoloDroid]

Hello again,
I just realized that there is a flaw in the above code. Instead of opening the table with

.Open "Dups1" ...[code]

you must open the query

[code].Open "SELECT MyField FROM Dups1 ORDER BY MyField ASC" ...

The key part is the "

ORDER BY MyField ASC

". As you can see in my code above, and my posts in the original thread thread181-480857 , I entered my test records in sorted order. I simply remove the field "

MyID

" for this example. In real life your data probably won't be entered in ascending oreder, so without modifying the above code you probably won't get the desired results.
Good Luck, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[faxof]

my friend if i could give you two stars i would - how about settling for a star and a kiss?

x

 

[SoloDroid]

Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

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

 

[faxof]

robert

thanks so much for your help on this, i have one last issue with it (hopefully its the last one anyway)

that macro that creates the 2nd table and removes the dups from the 1st table doesn't work unless the form that shows the table is close (so i close the form as part of the macro)

if i have the form open and i run the macro using the Run button in the design view of the macro it works perfectly - the form closes and the table are altered and i'm happy

however

if i use a button in the form to run the macro it doesn;t work properly; the form closes and it creates the 2nd table but when it tried to delete the dups from the 1st table i get this error message:

run time error '-2147217887 (80040e21)':
cannot update. database or object is read only.

the it hightlights this line from your code above:

.Delete adAffectCurrent


do you know why it works when i run the macro - but it doesn't work when i use a button to run the macro?

the buttonthat runs the macro doesn't do anything else but run the macro

bamboozled faxof