How to Remove Duplicate Records from table by removing dot (.)
Many of us have trouble removing duplicate records from our database tables. When data is imported from an excel file or from another database file, the database table will contain the duplicate records or data.
For example, there are two records for John (in the CustomerName field) with the same address:
- John M Lopez with address of 123 N First St
- John M. Lopez with address of 123 N. First St.
This means that there are duplicate records of John M. Lopez. This may happen because different users entered data in different ways. From the example above, the first user entered the name and address without a dot (.) as John M Lopez with address of 123 N First St. While the second user entered the name with dot (.) as John M. Lopez with an address of 123 N. First St.
The duplicate records for John, above, are alphabetically different. They cannot be found by using the “Find Duplicates Query” method. In order to find the duplicate records, we first need to find these records by removing the dot (.) from the name and address above.
Remove Dot (.) from table
There is an inconsistency of data entered in the Address field of tbl_Customer table as shown below. This data may be imported from an excel file or another database file. There are some dots after the direction N on the address. You will see the duplicate records for John Lopez with the same address. We need to remove dot (.) before using the Find UnMatched Query.
Create RemoveDot function
Place the RemoveDot function under the Module that can be called in any form. We use the Replace function; which is a built-in function for MS Access. The Replace function will replace the dot(.) in the text content with null or blank.
Public Function RemoveDot(strText As String) As String
RemoveDot = Replace(strText, “.”, “”)
Call RemoveDot() function
We need to create a button and name it cmdRemove. We will use the SQL Update statement to update the address field by replacing the dot with a null value under the click button. The completed code will be displayed like the picture below:
After you click the Remove dot(.) button, a message displaying “Remove dot(.) successfully!” will pop-up after removal is successful.
There is no dot(.) on the address field after clicking Remove dot(.) button. Now we will see two records of address 123 N First St that are exactly the same. You can use the same method to remove dot(.) for the customer name.
Using Find Duplicates Query (How to Create a Find Duplicates Query)
After removing the dot(.), we will use the Find Duplicates Query to get a list of addresses that are displayed more than once. Per picture below, I use the Find Duplicates Query for the Address field. There are two records that have the same address. I can delete one that I don’t want. More information about the Find Duplicates Query here.
Remove Dot (.) after Entering data
When we create a program, we can program it to eliminate the dot (.) when the user enters the address with a dot(.) for the address field. Through this way it can keep data consistent throughout the database. For example, the user enters an address for Justin White as 124 E. Main Ave., and we want to eliminate the dot (.) from the entered address and save it as 124 E Main Ave in the database.
Under the Form Design View, we can use the RemoveDot function or a built-in Replace function under the AfterUpdate event procedure of the Address field (see the picture below).
Private Sub Address_AfterUpdate()
Me.Address = Replace(Me.Address, “.”, “”)
Or Call RemoveDot() function
Private Sub Address_AfterUpdate()
Me.Address = RemoveDot(Me.Address)
You can also use macro with the RemoveDot() function shown below:
For example below, the user enters an address for Justin White as 124 E. Main Ave.
After the user has finished typing the address and hit Enter, the address will be updated to 124 E Main Ave without dot(.) after E or Ave as shown below. The data will be consistent with the same format before it is saved in the database.