Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a database in which there are company names each matched with the company post code. However there are duplicates since the company names are written multiple ways, like this:

Company Name        Zip Code
Sabuncu Ltd         55200
Sabuncu L.t.d.      55200

I would like to get rid of duplicates and have only one company name with a matching zipcode. I am using MS Access 2003.

I can provide more details if needed.

share|improve this question
1  
Are there any relationships connected to the Company Name fields of this table? –  CharlieRB Jul 19 '12 at 14:56
    
You might have better luck on stackoverflow. –  ioSamurai Jul 19 '12 at 15:06
    
No, there is no relationship connected to the Company Name field. –  Timothy Jul 19 '12 at 15:07

2 Answers 2

I think you might have to take the long route out of this my friend. The larger the relation the longer it might take, but I hope the duplicates are not as many. So basically, perform a select with the query wizard for all the company names and their respective zip codes only. The result will allow you to delete the erroneous data. Hope this helps. Provide more details if not...

share|improve this answer
    
I have created a select query and grouped by company name and zipcode. however since the company names can be written in different formats for the same company I have duplicates such as: –  Timothy Jul 19 '12 at 15:10
    
Clinic Madrid and Clinico Madrid or Hospital Nord and Hospitalate Nord and so on. And the data I have has 2000 rows. so it is not possible to see how many duplicates are out there. –  Timothy Jul 19 '12 at 15:13
    
The select query will allow you to view the records but will not allow you to edit/delete them. You would have to use an delete query, but be careful, deletes are permanent. –  CharlieRB Jul 19 '12 at 15:44

If this is a simple table which doesn't have any other tables connected to the Company Name field, then the safest way for you to do this is to open the table and sort it by the Company Name field. This will group all the like named companies together.

Then you can review the company names and remove the duplicate records manually. This will reduce the chance of data loss.

Beware; trying to automate removal of duplicates with a query will go through the table and permanently delete data. If not executed properly you can lose important data.

Just FYI; technically Access doesn't see them as duplicates because they are spelled differently. This is why I keep telling you to be careful with queries.

share|improve this answer
    
Do you mean sorting by the Company Name in the query? Or if you suggesting is some other sorting, how it will help me to get rid of the duplicates? –  Timothy Jul 19 '12 at 15:27
    
No, I am not talking about a query. I updated my answer. There is no safe way to automatically remove duplicates as Access doesn't know which one to keep and which one to get rid of. –  CharlieRB Jul 19 '12 at 15:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.