When maintaining an Admission database, it's a good thought to regularly remove indistinguishable records. Duplicate records can pop upward even with proficient data entry processes. For instance, the same client might be listed twice under two different account numbers. Or, two unlike customer records might take the same address in their Street Accost fields, only ane of which is correct. Thankfully, tape removal doesn't have to be a long and tedious process; you can find these discrepancies in seconds using the Find Duplicates Query sorcerer.

Using the sorcerer
To show how this technique works, we will utilize the Find Duplicates Query wizard to determine which customers in the Customers table, shown in Figure A, have the same accost assigned to their Street Address fields. (Note that while the sample table used hither has less than 20 records, the same technique can be applied to a database of any size.)

Effigy A

Begin by clicking Queries under Objects in the Database Window. Then, click on the New button in the toolbar to obtain the dialog box shown in Figure B.

Figure B

Select Detect Duplicates Query Wizard, and so click OK to actuate the starting time screen of the wizard, as shown in Effigy C.

Figure C

In this screen, the Customers table is selected as the tabular array to be searched for duplicates. Click Next to continue.

Figure D

The screen shown in Figure D is used to select the field nosotros desire to search for indistinguishable values. For this instance, Street Address is chosen, equally we want to eliminate indistinguishable mailings to the same address. Clicking Next brings upwardly the screen shown in Figure Eastward, where we select the fields to be displayed along with the Street Address field in the query results.

Figure E

In the final screen, shown in Figure F, we enter a name for the query, and then click Cease.

Figure F

The results shown in Figure Chiliad indicate that two different customers have been assigned the same street address, while 2 other customers accept each been assigned ii dissimilar client IDs. After further research, nosotros decide that the house number for Bernadette Williamson should exist 384, not 834. We can now correct the Customers table by manually deleting the indistinguishable records and making the necessary corrections to Williamson's street accost—right from the query results table.

Figure G

Deleting duplicates with Append Query
In the higher up case, only two duplicate records were found. Just what if the Customers database consisted of thousands of records, and afterward running the Notice Duplicates Query wizard, the results showed hundreds of duplicate records? Manually deleting all those duplicates from the query results table would exist highly impractical. Instead, you lot can utilize Append Query to have Admission delete them automatically.

Outset, create a copy of the structure of the tabular array that contains the duplicates. Click on the table name Customers in the Database Window, and and then click the Copy button in the toolbar. Next, click the Paste push button, which will display the Paste Table As dialog box shown in Figure H. Enter a proper name for the copy of the tabular array construction, equally shown. Under options, select Structure Only. Click OK to create the blank Customers Without Duplicates table.

Figure H

Open the Customers Without Duplicates table in Pattern View and alter its main cardinal to Street Address, as shown in Figure I. Making the Street Address field the primary key field will prevent Admission from copying records to the new table that have duplicate street addresses.

Effigy I

We are now ready to create an Suspend Query against the original table. Create a query in design view for Customers. Drag the asterisk (*) to the query design grid to include all fields from the original table. So select Append Query from the query-type drop-downward list, as shown in Figure J.

Figure J

In the Append dialog box, select the blank database Customers Without Duplicates, as shown in Figure Chiliad.

Figure K

Click the Run button. In the dialog box that asks whether y'all wish to suspend the records to the new file, click Yes. A dialog box like to the one shown in Effigy L volition appear, indicating that some records could not be copied considering there were duplicate values in the primary key field, in this instance, the Street Accost fields. Click Yes.

Figure 50

The query results table will have only 1 tape for each street address. When you are satisfied that the Customers Without Duplicates tabular array is right, you lot may delete the original table.

When to employ the wizard
As shown in this article, the Detect Duplicates Query wizard works well when it is used to check for indistinguishable entries in private fields and when the changes are small enough to exist handled manually; otherwise, it is more practical to use the Suspend Query when you need to delete large numbers of duplicate records. Nevertheless, to go on your data clean, it's a good thought to run the Find Duplicates Query wizard periodically; if yous do, y'all may never demand to run an Append Query.

Calling all Access masters

If you consider yourself an Access 2000 master, it'south time to ante up. Postal service a comment to this article and share your best Access tips and tricks.