Understanding duplicate data
One of the main reasons to use a relational database is to avoid duplicate data. However, since the age of databases, often acquire duplicate values, especially when multiple users enter data. Typically, deduplication saves money on storage and keeps your data accurate. This precision, in turn, can help you make better business decisions. For example, if you enter a sales order more than once, the customer can receive goods that do not need and that redundancy can increase the costs of shipping and accounting.
Duplication of what it really means
Prior to the identification and elimination of duplicate records, remember that you need the knowledge of the data. Unlike the process of designing a database, you can not follow a set of rules or procedures to find and delete duplicate records with specific precision. Remember an event when applicable: queries the database may return what appear to be duplicate records, but in fact these results are valid data. The apparent duplication usually happens when not include fields in the query that uniquely identify each record. For more information on how to include in a query the required fields and avoid duplicate false, see section when records are not duplicated later in this article.
Also, remember that you can not delete all the duplicate data because some duplications are necessary for the database to function properly. In other words, the database may contain redundancies necessary and unnecessary, and want to remove only unnecessary redundancy.
Necessary redundancies are often divided into two categories. The first kind of redundancy enables the database to the function. For example, data is duplicated in a primary key field when you need to establish one or several or many-to-many relationship between tables.
The second type of redundancy required arises when the database is used. For example, you can type the name of a city, a supplier or a common name like John Smith, many times. When this happens, it is not in danger of duplication because other areas of the database (such as primary key values, addresses and zip codes) contain sufficient information unique to prevent duplicate records are treated as data.
unnecessary redundancies can occur in several ways:
Two or more records may contain duplicate fields. They could usefully be two duplicate records but not all fields of the records contain matching values.
Although each record has a unique customer identifier (the value of the column to the left), the values of the name, address and city fields match. In such situations, even a partial match can be a good reason to use your knowledge of your business and review the records to see if they are duplicates.
Two or more tables may contain similar data. For example, you might find that a Customers table and a table named customers have records for the same customers.
Two or more databases may contain similar data. If you find that two or more databases contain similar data or inherited a database that overlaps with the current database, you must compare the data and structure of the databases and then take action to consolidate information.
Manually comparing databases, including those of small size, is not easy. If you need help with a comparison, there are several vendors that sell tools to compare the content and structure of Access databases.
Understand when records are not duplicated
In some situations, such as when the data returned by a query are observed, you see what appear to be duplicate records, although the underlying tables seem to contain only unique records. Problems can arise when the view does not include fields that uniquely identify a record.
As a rule, when a query is created, you must include the field or fields that uniquely identifies each record. Typically, a primary key field has this feature, but you can use combinations of other fields instead. For example, you have many instances of a contact name and numerous instances of a city name, but if a phone number and address are included, the combination of these data should be done every single record.
Factors to consider when working with duplication
Dealing with duplicate records depends on several factors:
The nature and extent of duplication ¿duplicates in a single table or two tables are similar (in the same database or two different databases) with data overlays They look? Or rather, duplicates in a view that is based on two or more tables are related records?
The specific requirements What do you want to do with duplicate data? Do you want to delete records from the database or just hide them from view? Or, would you count, average, or add the values of duplicate records? Does it matter which of the duplicate records are deleted or hidden? If so, would you review and delete records or delete records manually based on a condition? Do you also want to upgrade or consolidate one or more records before deleting records?
as answers to these questions (or types of questions), as well as their knowledge of the data, you should allow you to create a plan to deal with redundant data.
General preparations to remove duplicates
If you choose to remove duplicate records, you should treat the relations existing table. Normally, most databases use one-to-many. For example, you might have a few clients, but each client performs numerous requests. As a result, the table of customer data resides on the "one" side of the relationship and order data residing on the "many" side of the relationship.
Remember this rule when applicable: if you want to delete the data residing on the "many" side of a relationship, you can delete data without taking additional measures. However, if the data resides on the "one" side, you set a property in the relationship, or access prevent removal.
For more information about how to delete data from the "one", see the article Use a delete query to delete one or more records from a database.
Before you begin to eliminate redundant data, consider one or more of the following ways to prepare the database:
Ensure that the database is read-only.
Make sure you have the necessary permissions to modify or delete records in the database.
Ask all other users of the database to close the objects you want to work. This helps avoid lock violations.
Create a backup of the database before deleting records. Deletions can not be reversed or undone. The only way to recover deleted records is restoring from a backup. removal may also entail deleting records from related tables, so it is recommended to create a backup of the entire database before proceeding with it.