How to Eliminate Duplicate Records in an Access Query

By Lysis

Microsoft Access is a database application that allows you to use a wizard to create queries. These queries pull information from tables designed in the Access database console. The queries created by the wizard are automatically converted to a statement using SQL syntax. Small tweaks to the SQL statement in the query, however, can produce more efficient results in information retrieval. One issue that can result from using the Access wizard to create a query is that duplicate records are returned. Access has a keyword called "distinct" that eliminates duplicate records from the results. This word can be added to the SQL statement in the Access query, and then the records returned will always be unique.

Step 1

Open Microsoft Access and load the database that contains the query you want to edit. After the database loads, double-click the "Queries" icon. This opens a list of queries available in the database.

Step 2

Right-click the query that returns duplicate records. Select "Modify" from the list of options. This opens the query wizard.

Step 3

Click the "SQL" icon in the main menu toolbox located at the top of the Access window. This opens a new window that displays the converted SQL syntax for the query.

Step 4

Insert the keyword "distinct" after "select" at the beginning of the query. The keyword "select" is always the first word in a query that returns results back to the user. Placing the "distinct" keyword after "select" tells Access to return records that are unique and thus not duplicate.

Step 5

Click the "Design View" icon to return to the main wizard window. Click the "Save" icon to save the change. Close the query window, which returns you to the list of queries. Double-click the newly edited query to see the results. The returned records are now unique, with duplicates removed.