Getting rid of duplicates

[jen]

I posted this in 'Getting started' ng but it seems to have gone walkabout.
Sorry for repeating it here.

I've got a table with some few thousand email addresses.
I've done a query to find duplicates and there are THOUSANDS,
Using the result of the query I started to delete the duplicates manually.
Most are duplicated 4-5 times so are bunched together and easy to do.
But it's going to take me hours and hours.
Is there a way to run another query to append or delete or whatever all the
duplicates but leaving the one (of each) behind?
The email address column is the only one in this table so I don't need to
worry about other details
Thank you

 

[Guest]

Have you tried to show totals and groupby in a maketable query, then rename
the tables?

 

[jen]

No haven't really tried anything coz wasn't sure.
I know how to do a maketable query but wasn't sure how that would solve
deleting the duplicate records without having to do it manually.
So what you are saying is to maketable, that would list all the duplicates,
but show what totals? and group by what?
Jen

 

[Guest]

Right. Create a new query. Choose the table with the email addresses. Make
the email address field the query field (that's the only field, right?).
Under "View" on the menu, choose "Totals". Make sure the "Total" line on the
query grid (under the email field) says "Group by". Under "Query on the
menu, select "Make-Table Query". Name the new table something like "New
Email List". Save and run the query.

Then you can look at the "New Email List" table results to see if it did
what you wanted.

If it did, rename the old table the same name it was but with "old" on the
end of the name. Then rename the "New Email List" table to the old table
name. Then you can test and procedures that use the table. If something
isn't right, you can always rename back and try again, no harm.

Joe

 

[David Lloyd]

Jen:

The make table query would create a new table that contains only
non-duplicate emails. For example:

SELECT Emails.EmailAddress INTO FilteredEmails
FROM Emails
GROUP BY Emails.EmailAddress;

This creates a new table called FilteredEmails. Because the query groups on
the EmailAddress field, duplicates will be removed.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


No haven't really tried anything coz wasn't sure.
I know how to do a maketable query but wasn't sure how that would solve
deleting the duplicate records without having to do it manually.
So what you are saying is to maketable, that would list all the duplicates,
but show what totals? and group by what?
Jen

 

[jen]

Thanks you guys. I'm a dodo! I actually did it that way and had the list of
emails with another column with the no of dups each had but it didn't occur
to me what to do with it. The penny has now dropped. How simple!
Thank you so much for persevering
Jen

 

[jen]

It's me again!
Just one more thing. The new table will contain all the values that were
duplicated.
But what about the email addresses in the original table that were unique.
The new table won't list those.
So how do I retrieve them out of the original to add them to the new?
Ta

 

[jen]

I don't know if this was the correct way but it seemed to work
I ran the Find duplicate query but instead of greater than 1 I entered less
than 2 in the criteria.
Then I appended it to the new table.

Jen