MS Access™ MVP Awardee: 2007--2019
Visit My Blog: GPG on Access
My Published Books:
Professional Access 2013 Programming with co-authors Teresa Hennig, Ben Clothier and Doug Yudovich. Grover Park George On Access MS Access™ in a SharePoint World with co-authors Tim Runcie and Ben Clothier.
Over time, errors can creep into records in your database. For example, customers can be entered more than once in a customer table, perhaps by two different data entry clerks who don’t check for existing records*. There are many other ways duplicates can find their way into your tables, so periodically, you can check for them to keep your data as clean as possible. Fortunately, Access provides a Find Duplicate Query Wizard to help you do this. Start by selecting "Queries" in the Create tab of the ribbon. When the Query Wizard dialog opens, select "Find Duplicates Query Wizard" from the list and click "OK". Next, select the table (e.g., tblCustomer) which you think might have duplicate records in it. Click "Next" to move ahead. Select the fields which may have duplicate values. Be careful not to include primary keys, which can't be duplicated, by definition, or other fields which could be legitimately duplicated for two or more customers, such as addresses for several tenants of a commercial building. Click "Next" to move ahead. At the next page of the wizard you can add other fields from the table to help you decide on how to handle any duplicates that appear. Add any fields which will help you identify true duplicates. For example, two customers may share a name, especially if it's a common one like, "Jack Smith". Other fields in the record, like address, phone number, etc. can help you decide if they are the same person or not. Click "OK" At the final page of the wizard, name the query and click "OK" to see the results. Use a name that follows standard naming conventions, such as "qryDuplicateCustomer." *Of course, a properly designed interface can help prevent that problem.