Delete duplicate value from Access

Asked By 0 points N/A Posted on -

I have a Microsoft Access file. All data are unique here. But the operator inputs some same values in that table by mistake. Now I want to delete duplicate value from that table.


How can I delete all duplicate data from that table?

Best Answer by mubashir_hussain1980
Answered By 0 points N/A #116135

Delete duplicate value from Access


As it is seen that duplicate data is entered accidently or by mistake, to remove the duplicate data from the table in Microsoft Access you will have to create a query. Follow these steps:

  • Firstly, go to query section in the Access and then click on new to create a new query, There it will ask you different methods to create a query.
  • Select Query Wizard to find duplicates.
  • Click OK to see the first screen of the wizard.
  • The first screen of the wizard will ask to select the table name from which you want to find the duplicate values, select the table name and press next.
  • Now the wizard will come up asking that which field you want to search for duplicates, select the fields and press next.
  • Now the wizard will ask for other fields that you want to view along with the duplicated field in the result, select other fields and press Next. 
  • Now the final screen comes up asking you to name your query, put the name of the query in the text box and press finish.
  • Now the Table will show up with the records that are duplicated, you can delete the records or edit them up to your own choice.
Best Answer
Best Answer
Answered By 0 points N/A #116136

Delete duplicate value from Access



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.

  1. In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query in the list.
  2. 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.

  1. 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.


  1. 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.

  1. 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.

  1. 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.



I don't want to see these message boxes each time I run this query
If you followed all the steps, and you have permission to delete records in the database, and the database is not locked or read-only, the delete query will succeed in deleting the duplicate records. Open the Employees table in Datasheet view to verify that the duplicate record for Andrew Fuller has been deleted. If you enabled cascade deletes, open the dependent tables and verify that the related records have been deleted as well
Answered By 0 points N/A #116137

Delete duplicate value from Access


The following steps show how you can erase same data from the worksheet:

1. Make a copy of  your worksheet by selecting the work, then right click your mouse and choose copy. Go to a new worksheet, right click your mouse and select paste. Rename the new table, click structure only and press enter on you keyboard.
2. On the new worksheet, view it in design.
3. Choose the areas with the same data.
4. Choose the Primary Key option on the toolbar.
5. Save and exist the worksheet.
6. Contrive an append query as the worksheet with the same data, then in the Design View at the Query menu select Append Query.
6. Select the new name of the worksheet on the Append dialog box from the index and press enter.
7. Insert all the areas of the first worksheet by clicking on the star to the query design grid, then choose Run in the Query tool.
8. Select Yes on the dialog box that is explaining that you are about to append records.
9. An error message will appear. Choose Yes.
10. Look at the data of the new worksheet, confirm accuracy then delete the first worksheet change the name of the new worksheet with the one used on the first worksheet.
Answered By 0 points N/A #116138

Delete duplicate value from Access



Removing duplicate records in Microsoft Access tables can be tedious by querying or utilizing VBA Code. A simple Copy and Paste action can achieve the same results. 
You know that duplicate records can be a real problem for relational databases  and create headaches for administrators. Duplicate records in tables increase your database size, generate statistical errors, and decrease database tasks reliability.
Here is the procedure you would need to do:

Step 1

For us to start, make sure that all the tables are closed. Highlight target table ( table1) in the database, click Edit > Copy then click Edit > Paste, this will open the PASTE dialog box. Rename the new table (table2), click on the "Structure Only" radio button then click OK. Open the new table (table2) and locate the key field where the duplications are occurring and set that as the primary key field. Close the new table (table2).

Step 2
Highlight the original table (table1), click Edit > Copy then click Edit > Paste to open the PASTE dialog box again . This time select the radio button for "Append data to existing table". This will copy just the data from the original table (table1) to the structure of the new table (table2). 
Step 3
An error Dialog Box will open stating that records where lost due to key violations. Do not worry because these "lost" records are the duplicates we wanted to delete anyway. Choose "Yes" to Append data and the dialog box will go away.(here is a sample pic of the Dialog Box).
Open the new table (table2) and check for further duplication. You may need to repeat the procedure if there are further duplications in other fields of which I can assure you will contain only unique records as before. Delete or rename the original table to a new name (tableold) and rename the new table to the original table's name ( table1). 
Happy Computing,

Login/Register to Answer

Related Questions