Solved Find duplicates in Access 2013

[Sven1458]

Hi all,

At the office we have a data base of about 12000 customers. actually it is 12000 orders. some customer purchased from us several times. We want to send our customers a letter and introduce a new item that they may want.

the actual number of customers is about 8000 and it would cost a lot of money to send out the unneeded 4000 letters. So I am trying to find a way to eliminate the duplicates.

the database has several fields:

order # -- First Name -- Last Name -- Address -- City -- Zip

I could sort the database by address and take out the duplicates 1 by 1 witch will take forever.

I'm hoping someone here knows how to set up a query to do the job.
also, please tell me in plain English how to set it up.
Thanks
Sven

 

[Callender]

Duplicates in Access

Hi,

You will find a tutorial here:

Queries: How to Create a Find Duplicates Query

It's difficult to provide step by step instructions without being able to see the data. One problem is that finding duplicates requires exact text matches in the fields that you want to query for duplicates.

First Name and Last Name are no good unless they're unique.

Example:

C Smith
C. Smith
Chris Smith
Chris A. Smith
C.A. Smith

They might well be the same person but a duplicate query won't pick this up.

Address field:

123 Some Road
123, Some Road
123, Some Road, Some Town

A duplicate query won't pick this up either.

Find the most unique field (probably Zip) and compare that.

I find that sometimes the best way is to copy the table into Excel and use the find duplicates add on from asap-utilities to remove duplicates rather than using the remove duplicates function that's built into Excel.

With your duplicates removed you can copy the data back into a COPY of your Access database. (A cleancopy with the data removed before you re-populate it).

ASAP Utilities for Excel - Search in the description of all the utilities of this Excel add-in

Sorry, can't give step by step instructions as I don't use MS Office on my home machine. I only use it at work!

 

[Sven1458]

Hi Callender,
Thanks for your response.
Your first link < find Duplicates Query > is very detailed and is what I was looking for. I have a sample database at home and will try to make that work and hopefully will be the hero on Monday.
Thanks
Sven

 

[Ranger4]

In addition to what Callender has suggested you can look at some training videos that Microsoft supply & I find these very helpful. Click on the website below & scroll down paying attention to the ones on queries.

Training courses for Access 2013 - Access - Office.com

Queries are very powerful tools to use with your Access database & with practice & experimenting you can really get whatever information you want. The advantage of doing everything inside Access is that you can save it there for future use.

 

[Sven1458]

Hi Callender.

I set this up in no time and it works fine, How ever, I still have to remove the duplicates 1 by 1. it is better then before because I only have to look at about 1/3 of the records. Is there a way to automatically remove the duplicates and only have 1 left after it is done? Now that would be great.

Thanks
Sven

 

[Sven1458]

Hi Ranger4,

Thanks for the link, I will look at the videos.
thanks
Sven

 

[Sven1458]

Hi Callender.
I copied the table into Excel and use the find duplicates button to
remove duplicates. it looks like it is working fine.
Again, Thank you for your help

Sven

 

[Callender]

Remove duplicates

Hi again,

Before doing anything else it's a great idea to copy your access database so that you've got a copy if anything screws up.

That Excel add in that I mentioned can remove duplicate leaving only one entry. It's free for home use.

Other than that you can create an extra column in Access with the heading Duplicate and display that in your query results (add it as a field to display) - in fact choose to display all available fields.

When you view the duplicates query results you can copy the data into Excel by clicking the top left corner then Press "CTRL+A" then "CTRL+C" - switch to your excel spreadsheet and click in the left margin on the first row. Press "CTRL+V" to paste the data.

In theory it should be sorted in the correct order but if it isn't - highlight the field that contains duplicates and from the ribbon choose to sort in ascending order.

You should be seeing a pair of duplicate records followed by another pair repeated all the way down the spreadsheet.

Note: this example only shows one data field. Your spreadsheet should contain all available fields.



If you head to your duplicates column you can add a Y for the first few alternate rows like this:



Then highlight those first few cells and click and drag from the bottom right hand corner. Drag all the way down to your last entry.




You can then use the Autofilter to filter for empty cells. Here's how to set up the filter:

Excel 2013: Filtering Data - Lesson 19

So you highlight the header row and choose to filter. If you click the little arrow on the right hand side of your column headed Duplicate you can choose to filter blanks. Filter them out and copy the resulting data into another new spreadsheet.

Then in your Access Duplicates Query results window use the same method as before to highlight the data:

Click the top left corner then Press "CTRL+A then press DELETE.

This should remove all duplicate records entirely. Close the query.

Then in the main table - scroll to the bottom row.

Go back to your new de-duplicated Excel spreadsheet and copy the data into Access,

It's the same method.

Click the top left corner then Press "CTRL+A" then "CTRL+C" - switch to your Access Database and click in the left margin just below the bottom row (in other words the left margin of the next available empty row). Press "CTRL+V" to paste the data.



Or you can use the complicated method outlined here:

Eliminate duplicate records with this built-in Access query - TechRepublic

 

[Sven1458]

wow, very impressive,
I will take a look at that.
thanks again for your help

Sven

 

[Sven1458]

Or you can use the complicated method outlined here:

Eliminate duplicate records with this built-in Access query - TechRepublic[

If you read this thing it looks quite complicated, but I have done it several times just to practice, and it works just great. of cause it will not pick up any typing mistakes.

Thanks again for your help
Sven

 

[Ranger4]

One of the most important things with Access is getting the basic design right. It is actually better to have too many fields in your main Table than not enough.
In the OP's case having a phone number field would have made this a very simple operation, as the phone number would have been unique for each customer, so when running a query, typing "No Duplicates" in the "Criteria" section of the phone number field in the query would return just the one customer per phone number & multiple listings for each customer would be avoided.
Also the query could be saved & run again at any time that a new mailing list was needed.

 

[nishadhasan]

Removing Duplicates Files

Thanks author for this helpful post. Actually duplicate files are very bad for our PC or Laptop. It lose our valuable hard disk space. So, we all need to clear the duplicate files. There are many software on internet to clear the duplicate files. Not all work on windows and mac. Please choose that one which works both on windows and mac. Again thanks writer for this article.

Thanks
Nishad Hasan