Moving Duplicate Records

[Guest]

Hello,

I have been assigned the task of moving duplicate records from one table to
another table exactly like it. There are three fields that I am using to
determine if they are duplicates: Field1, Field2, and Field 3. I must leave
at least one unique record in the original table. Any suggesstions?

Our table has reached its limit and will not allow us to add anymore
(Currently about 1.8 million records). It's Access 97 by the way. If that
helps.

Thank You,

Tracy

 

[Gary Walter]

I have been assigned the task of moving duplicate records from one table
to
another table exactly like it. There are three fields that I am using to
determine if they are duplicates: Field1, Field2, and Field 3. I must
leave
at least one unique record in the original table. Any suggesstions?

Our table has reached its limit and will not allow us to add anymore
(Currently about 1.8 million records). It's Access 97 by the way. If that
helps.

you might try:

create a new db

in new db,
-- create table with same structure
but, *only index* on the 3 fields (no dups)
-- create link to old table
-- write append query to append data
from linked old table to new table

after first instance of specific Field1/Field2/Field3,
all other instances should fail to append

your situation may be more complicated than above,
but should work for info you have given....

 

[Gary Walter]

you might try:

create a new db

in new db,
-- create table with same structure
but, *only index* on the 3 fields (no dups)
-- create link to old table
-- write append query to append data
from linked old table to new table

after first instance of specific Field1/Field2/Field3,
all other instances should fail to append

your situation may be more complicated than above,
but should work for info you have given....

just to be clear, the *one index* is on all 3 fields together


select the 3 fields (hold down CTRL key and
click on each field), then click on primary key icon
in toolbar

 

[Guest]

Gary,

Your idea works great. Now I have a new table with all unique values in it.
How do I delete the old table and rename the new one?

Thank you,

Tracy McClarnon

 

[Guest]

Test

just to be clear, the *one index* is on all 3 fields together


select the 3 fields (hold down CTRL key and
click on each field), then click on primary key icon
in toolbar

 

[Guest]

That owrked great! Now how do I delete the old table and rename the new one?

 

[Gary Walter]

Truthfully, I am afraid to provide further advice
to someone who has been assigned to work with
a 1.8 million record table (where I work that would
be worth thousands of dollars!!), but does not know
how to proceed.

Backup...Backup...Backup...

Then in your new db with the new smaller table,
in Options, turn off "Autocorrect"

Tools\Options\General tab,
make sure checkboxes in "Name AutoCorrect"
are unchecked (or grayed out)

Then import everything from the previous database
except the big table.

Then, go into design mode for the new table
and set indexes as they were for original table.

"Tracy McClarnon" <wrote:

 

[Gary Walter]

More to the point of your question...

delete link to big table,
right-mouse click on new table
and choose "Rename" from menu...