Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Find duplicates in Access 2013

02 Feb 2014   #1

windows 7 ultimate x64
 
 
Find duplicates in Access 2013

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

My System SpecsSystem Spec
.

02 Feb 2014   #2

Windows 7 Home Premium
 
 
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!
My System SpecsSystem Spec
02 Feb 2014   #3

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
.


02 Feb 2014   #4

Windows 7 Home Premium 64 bit sp1
 
 

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.
My System SpecsSystem Spec
02 Feb 2014   #5

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
02 Feb 2014   #6

windows 7 ultimate x64
 
 

Hi Ranger4,

Thanks for the link, I will look at the videos.
thanks
Sven
My System SpecsSystem Spec
02 Feb 2014   #7

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
02 Feb 2014   #8

Windows 7 Home Premium
 
 
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.

Name:  PlanMaker - [Untitled 1].jpg
Views: 9
Size:  65.4 KB

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

Name:  PlanMaker - [Untitled 2].jpg
Views: 9
Size:  66.8 KB

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.

Name:  2014-02-03 01_13_27-PlanMaker - [Untitled 3].jpg
Views: 9
Size:  70.8 KB


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


My System SpecsSystem Spec
02 Feb 2014   #9

windows 7 ultimate x64
 
 

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

Sven
My System SpecsSystem Spec
07 Feb 2014   #10

windows 7 ultimate x64
 
 

Quote:
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
My System SpecsSystem Spec
Reply

 Find duplicates in Access 2013




Thread Tools



Similar help and support threads for2: Find duplicates in Access 2013
Thread Forum
Outlook 2013 cannot find Easy tranfer file from outlook 2007 Browsers & Mail
Word Document find and replace, find duplicates Microsoft Office
Need software to find duplicates in excel worksheets Software
Use formulas to find multi-column duplicates in Excel Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 12:50 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33