Find duplicates in Access 2013

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!

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.

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