Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Access Tip: Duplicate Records in Two Tables
By Richard Rost   Richard Rost on LinkedIn Email Richard Rost   16 years ago

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.

Comments for Access Tip: Duplicate Records in Two Tables
 
Age Subject From
12 yearsAccess create duplicate for a list total value onVyrn Evans
15 yearsduplicatesRichard Rost
15 yearsduplicatesCarolyn Quinn
16 yearsNo SubjectRichard Rost
16 yearsNo SubjectEric

 

Start a NEW Conversation
 
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Access Tip: Duplicate Records in Two Tables
Get notifications when this page is updated
 
 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 7:28:05 AM. PLT: 0s
Keywords: access tips update query delete action  PermaLink  Access Tip: Duplicate Records in Two Tables