How to Delete Duplicate Records in MS Access

We all eventually end up working on some table that someone designed that has no primary key, and has tons of duplicates. Luckily, we can use a few powerful techniques to either get unique rows out of that table (1 of 2: extract), or we can use some coding techniques to use the power of sorting with the ability to look at previous and current rows to identify and delete problem rows (2 of 2: delete). The two techniques in this video are very powerful for data cleaning and wrangling, and will definitely help you in cases where uniqueness is a problem in the data.
Related Videos:
How to Cycle Through Many TableDefs to Rename or Delete Many Tables in MS Access
• How to Cycle Through M...
How to Delete Duplicate Records in MS Access
You are watching this video now!
How to Add an Index to Your MS Access Table
• How to Add an Index to...
How to Use Composite Keys in Microsoft Access
• How to Use Composite K...
How RDBMS Relationships Work
• How RDBMS Relationship...
How to use Transactions in MS Access - BeginTrans, Commit, Rollback
• How to use Transaction...
How to Manually Enter Values into an Autonumber Field in MS Access
• How to Manually Enter ...
Replication in Microsoft Access
• Replication in Microso...
Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #duplicate #vba

[@seanmackenziedataengineering]

You can extend the coding example (2 of 2) to eliminate true duplicates by changing your sort query to sort by every column. The current example is for key duplicates. True duplicates example: "Wow it looks like someone is putting is rows where every field is the same.. I need to take care of those!" Key duplicates example: "Hey, it looks like someone created a duplicate record for employee 12345! And it looks like the address is different. Wait.. there are 1000 more like that! How did that happen? I just want to keep the latest one for each." Usually these situations can be fixed with some design changes to the application. For example, adding primary keys etc. BUT.. you still have to go and fix the data!

[@2000mgreen]

Thank you very much!!!

[@seanmackenziedataengineering]

Welcome!

[@tutsecret499]

Is that ok if you could provide the file so I can practice/apply the code related to your data file. Thank you again.

[@seanmackenziedataengineering]

No problem, look here: www.mackenziemackenzie.com/downloads and look for the 2022-08-16 entry.

[@tutsecret499]

Thank for this method. Is there additional method where the duplicates with additional conditional formatting with color highlight so we can see better the duplicates.

[@seanmackenziedataengineering]

Yes, you could add a field on the end and put in the color for that row as your loop progresses (without deleting). Then, you could use conditional formatting on a continuous form to show the color you intend for duplicate/non duplicate.

[@tutsecret499]

@@seanmackenziedataengineering Thank you. Yeah, the highlight is a must for better visual before I can delete.

[@karnabudhathoki5311]

The VBA Part....I presume it only deletes the immediate records that match...if I am not wrong....Because as we move to next recordset ; The last recordset that we compare attains the value of the previous record in the loop.Isn't it so ?

[@seanmackenziedataengineering]

You are correct, it deletes the immediate duplicates. However, since we use the power of sorting, the immediate duplicates ARE all of the duplicates. This is why we used the sorted updatable query for the record set.

[@artistryartistry7239]

Thanks for this. I thought a recordset object was literally a duplicate of the live data. Is that not the case? I'm confused as to how deleting a recordset item affects actual table data.

[@seanmackenziedataengineering]

Think of a recordset as a window to the live data. When you open it, the window "locks" the data being shown. So, when you loop through and send a delete command, you operate on the live data (through the window). When you directly open and view table or query data, Access is just opening a recordset and you are viewing it (through the Access interface). In code, you are opening a recordset in the same way, but it is behind the scenes and not viewable (except to your code).

[@artistryartistry7239]

@@seanmackenziedataengineering What a great explanation!!! Ok I get it now! Thank you!!!

[@MaraingMuong168]

Dear Sir. Could you please teach in new video how to drag a file PDF to Record in Form and Save and rename the file PDF that we want and save the file pdf to the location that we set up thank you sir I love your video and your teaching

[@seanmackenziedataengineering]

Interesting idea! I'll add it to my list. Thanks!

[@gerfer6261]

Vala👏👏👏👏👏 I am going to test this out Like the vba part on production table 1 ?= is it possible to run SQL inside the VBA procedure without creating a query for sorting please!!!!

[@seanmackenziedataengineering]

You can't run this one without the sorting. However, it is not absolutely necessary to create the sorted query object. You can instead pass a SQL string with sorting in it to the dbOpenRecordset statement, instead of the query name as you see in this example.

[@gerfer6261]

🤔🤔🤔

[@gerfer6261]

I tried it today, it does what it suppose to do. Good on you Sean 👍 I yet to try SQL part ( I like to compact two parts into one rather than two)

[@farinatty]

Hi Sean, what if the duplicate itrm has been assigned multiple items or events in the database. Have you got suggestions for code where the assigned items can be shown with the duplicate. I wolf then want to reassign them to the original. Sometimes you might choose not to assign them. Currently I do this manually. Hunt for the primary key if the original, hunt for the primary key of the duplicate. Find all the foreign keys of the duplicate. Change then to foreign keys for the original.

[@seanmackenziedataengineering]

This is a good approach. Just loop through and identify the ID you will keep from the parent record (A), then loop through the child records and reassign the foreign key to A for ones you want to keep, delete those you don't want until the child loop finishes, then delete the parent duplicates that now have no children.

[@gerfer6261]

Just a suggestion I will be very handy and appreciated if you add the code you presenting in a text format so that can be downloaded to make everyone’s life bit easier 🥸

[@seanmackenziedataengineering]

I'll post the code today; stay tuned.

[@seanmackenziedataengineering]

My (new) downloads page: www.mackenziemackenzie.com/downloads/

[@gerfer6261]

Thank you sir Really appreciated and will be beneficial to all come up here ✍️✍️✍️