Removing Duplicate Values

[armresl]

Hi Everyone,


I used the search function and found that the other posts didn't fit my criteria. What I have is a large file that was converted from db4 to access. There are a lot of lines of information and lots of the lines are duplicates i.e.

john smith 1234 main st Portland, OR 90125 818-555-1212

there might be 10 of that entry. Is there a way to remove the duplicate entries and then resave the directory?

Thanks for your help.


Rob

 

[trucktime]

Duplicate

I assume we're talking about duplicate records here.
I'm no expert in this, but here is my idea.

Create a duplicate of your table (copy, structure only).
Set one of the key fields to Index, No Duplicates.
Probably a good field to do this on is phonenumber,
since it's not likely that different people in your table have the same number.
It has to be a field where records cannot have the same value.

Then create and run an append query, append the data from the old
table to the new one.
When you run the query you'll get a warning, saying that not all
records can be processed (which is what you want)
Just click OK to run the query. That should do it.
In the new table no 2 records with the same phone number should exist.

Your old table stays intact (always good to make a backup anyway),
so you can check and see if you didn't loose any date you need.

Good Luck.

In case someone has a better method, I like to hear about it.

 

[armresl]

Thank you, I will try that first thing in the morning. Have a good evening.

 

[FLabrecque]

What I do is this:

1. Create a copy of the table (structure only)
2. Execute a query like this:
INSERT INTO TABLE2
SELECT Name, Address, Etc
From TABLE1
GROUP BY Name, Address, Etc
3. Replace the old table by the new one.

 

[armresl]

Thanks for the posts.

Let me add a new wrinkle to this.


Say there are 10k names, addresses, phones, emails, etc of that there is a company

Company XYZ


Company XYZ has 500 employees but I only want to delete instances of repeats over 10 people.

So not necessarily the repetition of a name but just 10 entries from the same company i.e.

tech dept
mailing dept
HR
accounting
housekeeping
maintenence

etc just want to remove anything over 10 instances for a company not necessarily a duplicate name



WHEW hope that came out right.


Thanks again.

 

[MCantu]

What now?

You may have duplicates that are not true duplicates because they contain unique information?

OR- you only want the top ten entries for that company. if they have more entries, you don't want them?

 

[armresl]

I don't know that it has to be the top ten. But say anything over 10 or 15 so that they company doesn't get several items as opposed to just a few.