How to remove duplicate rows in microsoft access - 3 different ways

How to remove duplicate rows in Microsoft Access - 3 different ways
In this episode, we're going to look at three ways that you can remove duplicates from your query and report output in Microsoft Access. Duplicates rows are often a pain for those who need to make accurate reports and queries for analysis. It helps when you have a few ways that you can use to reduce the duplicate rows.

First, we'll look at what are called "composite" keys, which is a situation where more than one field makes up the "key" of a table. The key is how we determine how each row is "unique" in a table. Second, we'll look at how to use the Distinct keyword in our query, to limit our output to only unique rows. Finally, we'll look at how to change our query to a "summary" query to not only remove our duplicates, but also give some additional, meaningful information in our output.

I am planning a second video on duplicates, in which I will give a VBA coded solution to remove duplicates directly from table data. If you're interested in this solution, please let me know in the comments below!

Follow us on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

Interested in transforming your career or finding your next gig?

Want my team to do a project for you? Let's get to it!

MS Access Project? Contact me today!

Check out some of my earlier videos on MS Access:

" How to Add Combo Box Rows On-The-Fly in MS Access":

[sebastianhernandez]

the best, thank you man for this video, literally I was struggle with the duplicate record from most of my queries and those tips save me from a mental collapse. HOpe that you continue publishing videos like this.

[ortensianorton]

Thank you! You just saved me hours of work!!

[satyabanukil]

Sir, hope you are fine. Now I am developing a database based on lot of tips received from you at different point of time. Therefore, dont want to disturb you frequently. But this time, looking for a difficult task and what I feel that it may be possible by application of looping about which I dont have any idea as never did before. This relates to allocation of team. Suppose Team A, B, C and D are in a competition. Team A has faced Team B, C but not D. I have all the four teams in my database. I want first to check whether A has faced B in Session I. So in Session II, A cannot face the same team. So either it has to be within C and D. Finally, I want the system to loop through all the records in relation to A and check whether A has played with B. If he finds that A and B met earlier, then it will display a message "Both them played earlier". Then the system will check the next which is C. If they have not met earlier then system will automatically select C. Sir, I want to know whether it is is possible and if so a tutorial on it would be very helpful for all of beginners like us. Thanks and Regards.

[shafameen]

Very useful video indeed. I am curious to ask you about your opinion on Microsoft Powerapps. Where does it fit in with Acess or Power BI? Is it worth it to invest in learning it as a data/reporting analyst? Cheers

[tutsecret]

How I would highlight the row (not only the cell) where there are duplicates in form view. I want to find, but not remove yet. I need to review all the duplicates first then I delete. Somebody here made a mistake and add 100 wrong rows that were already in the database and will create duplicates. I want to see the duplicates highlighted.

[wwaterski]

Hi Sean, I would like to see how to delete the duplicate fields. But better yet, only show the Max Date and not add the Total revenue. Would you just keep the Revenue as Group By?

[notinvesting]

I got issue on my sql search subform whether i try groupby or Dmax on it. My field import date have multiple date records which i don’t need. All i need it the code solution based i will paste my code below thank you for your kindness in advance.