Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
NEW: Excel Expert 11 - Learn XLOOKUP    dismiss
Access Tip: Duplicate Records in Two Tables
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   7/28/2008 3:21:03 PM

Q: I have two tables with customer information. I need to keep the two tables separate, but I would like to delete the duplicates from one of the tables (i.e. delete the customers from table A who are also in table B)?

A: There's really no easy way to take two tables and delete the duplicate records between them. There is a FIND DUPLICATES query, but that only searches for duplicate RECORDS in a single table... which you could do, if you could merge both tables together and then delete all of the duplicates.

Here's my cheap workaround for finding duplicate records between two tables. This assumes you have a field in them (such as CustomerID) that is the same for both tables.

Step 1: add a field in the table that has the records you want to delete called DeleteMe - or something to that effect.

Step 2: JOIN these two tables together in a query by whatever field is related so that when you run the query you see only records that are in both.

Step 3: convert this query over into an UPDATE query. Now, update the value of the DeleteMe field to YES. See this tutorial for help on Update queries:


Step 4: run the query. Now every record that is in BOTH tables (since they're joined) will be marked YES for DeleteMe.

Step 5. Make a new query based on just the table with records that need to be deleted, and set the criteria so DeleteMe = TRUE.

Step 6. Convert this query over to a DELETE query. This should delete only those records.

If you need more help with ACTION QUERIES (make table, delete, update, append, etc.) I cover them in exquisite detail in my Access 222 Tutorial:


Hope this answers your question.

Access create duplicate for a list total value on Link 
Vyrn Evans - 10/2/2012
Your site looks nice and logical and helpful.
Is it possible to create a duplicate of the value of a totals or running sum from the footer of my subform (frmCustomers) to my main form (frmCourses)? Everything I've tried has failed so far. Tried 'Me' with dots and exclamations in builder, tried to run a query, no luck. Any advice or solution gratefully received. MS Access 2007/2010. Read More...
Add Your Comments
duplicates Link 
Richard Rost - 7/29/2009
Carolyn, you can import them all into Access and then use Access to delete the duplicates. There is a FIND DUPLICATES query wizard. Try that.
Add Your Comments
duplicates Link 
Carolyn Quinn - 7/28/2009
I have a table full of dupes.  Oh my goodness.  There are so many, yet they have their own uniqueness.  I am not sure how to delete them or if i should delete a few at a time; for instance, they all have an account name, Regional VP, Regional Director, Acct Number, budget number, Division VP and opening date.  These fields are all thed same for every account.  Then it has six programs, then they have about 8 brands, and then the 8 retail items.   This information is in Excel and i know it needs to go to Access, but i need to delete the dupes first.  It hard be cause one category says coffee, but there are 12 different coffees to choose from and one account could have all of them which is why the duplicate lines because each lines signifies another type of coffe.  I want to stop and start a database with relationships, but time won't permit me for this first report, but going forward i can.  What options do i have?
Add Your Comments
Richard Rost - 9/8/2008
Eric, I've got no idea. I'd have to see this database to tell you for sure. If the whole form just goes BLANK, that tells me that Access is out of system resources (memory). Have you tried just rebooting?
Add Your Comments
Eric - 8/31/2008
I am trying to create a form with multiple combo boxes from several different tables.  I can do it with one combo box from one table, but as soon as I add a third, the table becomes invisible in form view, even though I can still work with it in design view.  Any suggestions?
Add Your Comments

Show Older Posts


Post Your Comments
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

5/30/2020Two New Access Handbooks
5/30/2020Access Tip Sleep Timer
5/18/2020Access Record Source Property
5/3/2020Access Replace Function
5/1/2020Microsoft Access Tutorials
4/24/2020Access Discontinued?
4/21/2020Date Time Clock Picker Template
4/20/2020Access Backup Record Before Edited
4/17/2020ZIP Code Lookup Template
4/16/2020Access Round Nearest 10

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed   
Keywords: access tips update query delete action  PermaLink