deleting duplicate phone numbers from table

M

magiccostarica

I did a duplicate query to find duplicates in a file,
the file has 30,000 plus duplicates,
i need to delete all duplicate phone but leave just one

for instance if a have 8 of the same numbers i want to leave just one and
delete the other 7

how can i do this without having to do it manually
magic
 
J

John Vinson

I did a duplicate query to find duplicates in a file,
the file has 30,000 plus duplicates,
i need to delete all duplicate phone but leave just one

for instance if a have 8 of the same numbers i want to leave just one and
delete the other 7

how can i do this without having to do it manually
magic
Click to expand...

The simplest way is to create a new table with a unique Index on the
Phone field, and run an Append query appending the phone numbers into
it.

What will you want to do if you have two different sets of *OTHER*
data (PersonID, names, whatever) with the same phone number? Just
discard some of the data arbitrarily?

John W. Vinson[MVP]
 
K

KARL DEWEY

If you have other data like additional people at the same phone number you
might consider using two tables - Phone and People.

First try finding duplicates on phone & name.
 
M

magiccostarica

i do not understand? can someone please help me to do this step by step as i
am new to access, i have asked numerous times but people keep talking in
computer lingo and i am stupid to that

i have 30,000 phone numbers to delete and am going blind doign it one at a
time
PLEASE SOMEONE HELP
benjamin
 
J

John Vinson

i do not understand? can someone please help me to do this step by step as i
am new to access, i have asked numerous times but people keep talking in
computer lingo and i am stupid to that

i have 30,000 phone numbers to delete and am going blind doign it one at a
time
PLEASE SOMEONE HELP
benjamin
Click to expand...

Back up your database by copying the .mdb file to another file, just
for safety.
Open the Tables tab on the database window.
Right-mouse Select your table. (I don't know its name or what fields
it contains, so I cannot be more specific).
Choose Copy from the dropdown menu.
Right mouseclick the blank area of the screen and select Paste. You'll
get the option to paste with or without data (design mode only);
choose this option - you want a copy of your table with NO records in
it.

Open this new table in design mode.
Select the Phone field.
On the Indexes tab at the bottom of the screen, select "Indexed - No
Duplicates".

Save and close the table.

Open the Queries tab, and create a new Query. Base it on your original
table.

Select the Phone field (and any other fields that you want to copy...
again, I cannot see your database and do not know just what you want
to accomplish).
Change the query to an Append query (using the Query menu option or
the query-type tool on the toolbar).
You'll be asked what table you want to append to - choose this new
table.
Run the query by clicking the ! icon.
You'll be warned "xxx records failed due to index violation" or some
such message - click OK; that's your duplicates being thrown in the
bitbucket.

Check the new table to see if it contains the data you want, with one
record per phone number.

John W. Vinson[MVP]
 
Top