Microsoft Office Professional ships with the MS Access database application that lets you analyse and manage large amounts of data better than say an Excel spreadsheet. Sometimes, the information stored in various tables becomes fragmented causing duplication and at some point will need to be cleaned up.

This is where distinct queries can help. Since a query asks questions to the database tables, it can also control how the information will be displayed in the output and that also includes the removing (or at least supressing) of duplicate values.

Distinct Queries? How To Create An Access Database Query And Make It Unique

There are two types of distinct queries. The first one is simply called Distinct and will look for unique values within the fields chosen for your query output and ignore all other fields from one or more tables. The other query type is known as DistinctRow and will include all the fields no matter if they are added in the output for your recordset.

The SQL code of the two types begins with:

SELECT DISTINCT....

SELECT DISTINCTROW...

You can either set this manually in the SQL view mode (as shown above) or set the correct properties via the QBE (Query By Example) grid using either the Unique Values or Unique Records which both have a ‘Yes’ or ‘No’ value. However, only one can be set to ‘Yes’ at a time and therefore forces the other property to be set as ‘No’.

To create this type of query in your database, start by identifying your tables (and have them joined together to form a logical relationship) in the design view.

Add the fields and quickly run to test the data is being displayed. Next, add criteria and run again making sure there are no logical errors (or wrong data output results).

Now decide which of the distinct queries you want to apply and open the property sheet (or properties window for earlier versions of Microsoft Access). Make sure the cursor is in the upper half of the design windows and locate the desired property to set as ‘Yes’.

Run your changes and then adjust any other attributes before saving.

This is great for trapping duplicated data like repeating countries from a customer list or categories for a type of transaction where there is no dedicated lookup table due data being imported from external systems. What other uses do you use that you would like to share in the comments box below?

Tags: create an access database, creating an access query, distinct access query, distinct queries, how to build queries, unique and distinct in sql