Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Courses   TechHelp   Templates   Forums   Help   Order   Contact   Logon  
 
 
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:

  /tips/access/update-query

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:

  /site/courselist/access/access222


Hope this answers your question.

Access create duplicate for a list total value on Link 
Vyrn Evans 
10/2/2012
Hi
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 a Reply
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 a Reply
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 a Reply
Link 
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 a Reply
Link 
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 a Reply

Show Older Comments...
View in Table Format

Post a New Comment or Question
 
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.
 
Subscribe
 

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

8/14/2020Access NZ Function NULL to Zero
8/14/2020Access AfterUpdate & DLookup
8/14/2020Microsoft Access Crash Course
8/14/2020Access TechHelp
8/13/2020CopyFileA Function
8/13/2020Access Turn Off Warnings
8/13/2020MsgBox Prompt Yes No Cancel
8/12/2020Navigation Pane Minimize Restore
8/12/2020Ribbon Minimize and Restore
8/12/2020Application Window Maximize Restore
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search The Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
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