Duplicate addresses

[Sully38]

I have a table with 400,000 records that was imported from Excel. The file is clean and contains Comp. Name,Contact,secondary Cont, address, state,zip. I am trying to remove any and all duplicate records from this file based on Company name, Conatct, then secondary contact. I have tried the duplicate records query but that removes all the records. How can I remove all the dupes but leave one of the record. Is this possible through queries or do I have to code a procedure. Any Help is appreciated.

 

[Joe4]

You can use a Make Table Query to create a new table that removes all duplicates. Here is how:

1. Build a query, selecting all the fields you want to use in the dup checking
2. Click on properties, and toggle the "Unique Values" value to "Yes"
3. Change the query type to a Make Table query
4. Run the query, entering in the name of your new table

 

[Gates Is Antichrist]

The Find duplicates query wizard should have worked just fine, while I like jmiskey's approach too, though I try to make queries fully "visibly" defined in the query builder because I fairly rarely view the SQL (though sometimes you have no choice). (And until that post, I didn't even know there WERE properties on queries. It took 4 tries for me to find them!)

Anyway I suspect your duplicates really aren't duplicates. If you think there are 12 records for company "Gulf Oil" in field [Comp name] in the table, try this test. I'll base this on the query builder but you can work straight from SQL if you want. Start a new select query, make your only field
"<" & [Comp name] & ">" and run it. Then rerun it after hitting view/totals. The difference between the two results should reveal something - perhaps impelling you to get busy with Trim or Upper or something.

The acid test would be doing the above test with this sole field:
"<" & [Comp name] & ">" & "<" & [Contact] & ">" & "<" & [Secondary cont] & ">" & "<" & [address] & ">" & "<" & [state] & ">" & "<" & [zip] & ">"

Unless you misused the wizard in the first place, I don't think that jmiskey's approach will get you any further. (And again, jmiskey, your advice is always fine.) I suspect you'll want to try something like the technique I've shown. Hopefully it will help you get a "better handle" on the data, or point you to a better definition of the duplication "definition."

Considering the chances of those particular 6 fields being spelled, nicknamed, 5-digit vs. 9-digit, etc. EXACTLY the same ... I'm not at all surprised that the duplicate query found nothing.

 

[Sully38]

Awesome responses.. project worked like a charm..Thank you both

 

[Gates Is Antichrist]

Well, good, whatever you did. I just noticed that I neglected to state that the above "testing" / data analysis is made more practical by using some degree of limiting criteria. IOW I should have said, put "*Gulf*" in the company name criteria above.

AAR you're welcome, and thanks for reporting back.