Removing Duplicate Records in Microsoft Access. This article was originally written for Access 2. Parts of it might not apply to current versions although it is maintained here for reference. Introduction. Occasionally, you might have a table in Access that contains duplicate records. Maybe it was imported from one or more sources or perhaps it was not setup with the necessary key to ensure unique records. Whatever the reason, now you want to make sure that the records in the table are unique. Fortunately, Access offers a couple of options that you can use to remove the duplicates from your table quickly and easily. This article will explain two methods for removing duplicate information from tables. Please remember that you should always backup your database before making changes to database objects or deleting large amounts of data. A sample database with the tables and queries shown here is available for download. Download sample for Access 9. Download sample for Access 2. Throughout this article, I use links to various screenshots which will be displayed in a separate window. An index of these screenshots is available at the bottom of this page. As the example, Im using a small table of fictional names, addresses and telephonenumbers. Figure 1 shows a screenshot of the original table which contains duplicate records as shown by the highlighting. For the purpose of this example, Ive only included thirteen records and it would be simple to pick out the duplicates manually but if this table contained hundreds or even thousands of records, that would probably not be an option. Figure 1 Original Contacts table with obvious duplications highlighted. Find Duplicates Query Wizard. The first way to remove duplicates from a table is to use the Find Duplicates Query Wizard which is available by clicking on the New button in the database window or by selecting Insert and Query from the menu bar. Figure 2Figure 2 Accessing the Find Duplicates Query Wizard. After selecting a table or query in which to find duplicate records, the wizard will prompt you to select a field in which the data should be unique for each record Figure 3. In this case, the home phone number field is the best available. While you could use the addressfield to narrow the table by household, the phone number is shorter and is stored in a standard format. Chinese B3 Air Rifle Manual. It is much less likely to contain errors or abbreviations that will prevent the wizard from finding a duplicate. You can also select a combination of fields if there is no one field that will ensure a unique record. Figure 3 Query Wizard Selecting unique field. The next screen Figure 4 will enable you to select any other fields you want to include in your query. In this illustration, all of the fields in the table are selected but you can select as many or as few as necessary. These fields will not affect the identification of duplicates but they will make the query results more understandable. If you do not select any additional fields, the wizard will add a field of its own that will count the number of duplications for each value it finds in the field that you selected earlier. Figure 4 Query Wizard Selecting accompanying fields. The next screen will enable you to enter a name for the query to be saved under and will give you the option of viewing the data or changing the design of the query. Microsoft Access Append Query No Duplicates Sql' title='Microsoft Access Append Query No Duplicates Sql' />When you run the query, the results will look something like Figure 5. Figure 5 Query Wizard Find Duplicate Query results. From the textbook Microsoft Access 2010 Comprehensive. Learn with flashcards, games, and more for free. Good morning Experts, I have a question regarding the proper method of appending data to a table without adding duplicates. For example, with two tables. The steps for creating a pivot table from multiple worksheets both in the same file are 1. Suppose the workbook with the two worksheets Jan and Feb is named. DATASTAGE Frequently asked Questions and Tutorials DATASTAGE Frequently asked Questions and Tutorials 1. DATASTAGE QUESTIONS 2. DATASTAGE FAQ from GEEK. Advanced Result Sets Large Objects Linked Tables Spatial Features Recursive Queries Updatable Views Transaction Isolation MultiVersion Concurrency Control MVCC. Interested in learning more about Excel VBA MrExcel will teach you how to use Microsoft Excel VBA. Check out our site for more information about Excel VBA tutorials. You will notice in this example that the query has actually found three sets of duplicates. In addition to the two duplications which were highlighted in Figure 1, the table also contains the same phone number for Harrison and Geneva Raines. As I mentioned earlier, the wizard will allow the use of more than one field while searching for duplicates. In this case, using the first and last names in addition to the phone number would have eliminated the two highlighted records. With a database like this, you may or may not want to allow more than one contact per household, depending on your use of the data. Either way, the query has found the duplicated information in the table according to the specified settings. Depending on the size of the table, this method may be all that you need. If the query finds no more than a few dozen duplications or if you want to remove both the original and duplicate record, its easy to delete the necessary records from the query. The records are automatically removed from the table on which the query was based. On the other hand, for especially large tables where you only want to remove duplications and leave the original record in the table, this may not be enough. Article continues after adMarion Technical College in Ocala, Florida offers a variety of vocational and career programs, including Database Development and Programming. Find out more on our site at http www. Using an Append Query. The other method of removing duplicate records is a two step process that involves creating a copy of the table and once again selecting a field or combination of fields to determine if each record is unique. The first step is to create a copy of the table structure. To do this, select the table in the database window and then select Edit and Copy from the menu bar or right click the table and select Copy from the pop up menu that appears. Then paste the table back into the database window, once again using either the menu bar or the pop up menu. You will see the dialog box displayed in Figure 6. After entering a new name for the table, select Structure Only from the paste options shown. This will create a copy of the table structure but will not transfer the data. Figure 6 Copying the table structure. To select the unique field, open the new table in design view and select the appropriate field from the list shown. Right click the field and select Primary Key from the pop up menu or use the same option on the menu or toolbar Figure 7. A tables primary key is used to uniquely identify each record. Access will not allow duplicate information to be entered in a field marked as the primary key. In this example, Im still using the phone number as the unique field but a combination of fields can also be set by holding down the SHIFT or CTRL key, selecting more than one field and setting it as the primary key using either the pop up menu, menu or toolbar. Save your changes to the table structure and then close the table to return to the database window. Figure 7 Choosing the new primary key. The next step is to create an append query that will transfer the data from the original table to the new table structure. An append query must follow the same rules set by the primary key as you would if entering data manually. This means that it will not be able to copy duplicate records to the new table. Only one copy of each record will be transferred. To create the append query, create a select query from the original table, including all records in the table as shown in Figure 8 below. Right click in the top half of the query design area and select Query Type from the pop up menu and then select Append Query. This will change the query type while retaining the table and fields that youve inserted. Figure 8 Creating the append query. You will be asked for the name of the table to append the data to Figure 9. Select the name of the empty table that you just created and click OK. Figure 9 Selecting the table for the append.