Delete duplicate value from Access

Delete duplicate value from Access

Hi!

Before deleting duplicate entries you must find out how much records are really duplicated – whether completely or partially.

Then try to delete them by the following steps:

Hope it works for you.

To create the delete query    

1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design view, and then click OK.
3. In the Show Table dialog box, on the Both tab, double-click the Employees table and the Employees – Distinct Records query. Then click Close.
4. Join the two tables on the Last Name field. Drag the LastName field in the Employees table to the LastName field in the Employees – Distinct Records query.

Important: Joining the two tables is necessary to avoid the "Could not delete from the specified tables" message that you will otherwise get when you run the query.

5. In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query in the list.
6. Drag the asterisk (*) from the Employees table to the Field row of the first column.

You now see Employees in the Table row and From in the Delete row. This means the query will delete rows from the Employees table.

7. Now you need to specify which records must be deleted. The Employees table contains all distinct and duplicate records, whereas the totals query returns only distinct records. The records in the Employees table that are not returned in the totals query are the duplicate records, so you'll delete them.

Add the Last Name, First Name, Birth Date, and Hire Date fields from the Employees table to the grid. Next you need to specify the criteria. To avoid typing the long query name in each Criteria row, assign a short alias to the totals query. Right-click the title bar of the totals query window that lists the fields in the totals query, and then click Properties on the shortcut menu. Set the Alias property to Distinct and close the property sheet. Now type the criteria in the columns in the grid. In the Criteria row of the Last Name column, type [Distinct].[LastName]. In the Criteria row of the FirstName, BirthDate, and HireDate columns, type [Distinct].[FirstName], [Distinct].[BirthDate], and <> [Distinct].[MinOfHireDate] respectively, as shown in the following illustration.

8. Click View  to preview the records that will be deleted when you run the query. You will see the record of Andrew Fuller that has the later hire date. Save and close the view.

Important    The field in the totals query that uses the First, Last, Min, or Max function is the field that separates the records that you want to keep from the ones that are duplicates (that you want to delete). If this field has the same values for two duplicate records, the delete query will not work properly. To be specific, if there are two duplicate employee records with the same hire date, the query will not delete either of the records, even though the underlying totals query returns only one of the two duplicate records. If you expect that the HireDate field may often contain the same values for duplicate records, you need to change the design of the totals query so that it instead uses the HireDate field as a GroupBy field, and some other field with the First, Last, Min, or Max function.

9. There is one more step to perform before you run the query. You need to review how records in related tables will be affected when the employee record is deleted. If you run the query without doing this step, the query will not be able to delete any records, due to key violations.

In the Database window, right-click the window background, and then click Relationships to open the Relationships window. Identify the tables that are related to the Employees table.

You see that the Orders table is related to the Employees table. Each record in the Orders table contains the ID of the employee who handled the order. If you delete an employee record, the orders that reference the ID of the deleted record will then reference an invalid ID. To avoid this, you should review and edit the records in the Orders table to make sure that all orders handled by Andrew Fuller reference the ID of the record that you are going to keep.

In some cases, the appropriate thing to do will be to delete all dependent records. For example, if you are deleting a category record from the Categories table, you might want to delete all the products that fall in that category from the Products table. To make sure that the delete query will delete all dependent records, double-click the middle segment of the line joining the Categories and Products tables, and see if the Enforce Referential Integrity and Cascade Delete Related Records check boxes are selected. If not, select both. Repeat this step for all tables that depend on the Products table, and so on, until you account for all dependent tables.

10. Close the Relationships window and return to the Database window. Find the delete query and double-click it. Click Yes twice to confirm that you want delete records by using the query.