Others

How to Count Distinct Values in Microsoft Access SQL

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to count unique values in a Microsoft Access database table but encounters an aggregate-function error because the standard COUNT(DISTINCT) SQL syntax is not supported.

Product: Microsoft Access
Device & OS: not provided
Scenario: Writing an SQL query to retrieve the total number of unique records (such as distinct prices) from an Access table.
Observed behavior: Executing a query with COUNT(DISTINCT field) results in an aggregate-function error, as Access SQL handles distinct counts differently than other database systems.

Before you start

Ensure you have your Microsoft Access database open and identify the specific table name and the field name (e.g., 'Price') you intend to query.

Solution 1Recommended

Use a Saved Query to Count Distinct Values

This is the most reliable and highly compatible method in Access. By creating a distinct query first and then counting its results, you avoid subquery syntax errors.

Microsoft Access often struggles with nested queries containing DISTINCT clauses. Breaking the task into two separate queries ensures maximum compatibility across all versions of Access.

Create a distinct query

Go to the Create tab, select Query Design, switch to SQL View, and type: SELECT DISTINCT Price FROM tblTableName;

Save the query

Save this query and give it a memorable name, such as qryDistinctPrices.

Create the counting query

Create a second query in SQL View that references the first one: SELECT Count(*) AS TotalDistinct FROM qryDistinctPrices;

Run the query

Click the Run button to execute the second query and view your distinct count result.

Compatibility: This two-step method is guaranteed to work even in older versions of Microsoft Access where nested subqueries fail.

Solution 2

Count Rows from a Nested Subquery

This method uses a single SQL statement by nesting the DISTINCT selection inside a COUNT query. It is faster to write but may cause errors in certain Access versions.

Solution 3

Run a Distinct Query and Count Manually

Best for small datasets where you simply want to list the unique values and quickly check the total count without writing complex aggregate functions.

Free Microsoft Office alternative

Manage Data Easily Without Complex SQL in WPS Office

While Microsoft Access requires complex SQL workarounds for simple tasks like distinct counts, you can easily manage, filter, and analyze data using WPS Spreadsheet. WPS Office is a lightweight, free alternative to Microsoft Office.

1. Open your data in WPS Spreadsheet: Export your database table to an Excel format and open it with WPS Spreadsheet.
2. Use Remove Duplicates: Select your column, navigate to the Data tab, and click Remove Duplicates to filter unique records.
3. View the count: Highlight the remaining column data to instantly see the distinct count in the bottom status bar.

Fully compatible with Microsoft Excel (.xlsx) formats.Easily count unique values using Pivot Tables or the Remove Duplicates tool.Free and lightweight Microsoft Office alternative with a familiar interface.No complex SQL coding required for data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNT(DISTINCT) give an error in Microsoft Access?

Unlike SQL Server, MySQL, or Oracle, Microsoft Access SQL does not natively support the COUNT(DISTINCT field) aggregate function. Attempting to use it triggers a syntax or aggregate-function error, requiring workarounds like saved queries or subqueries.

How do I select unique records in Access without counting them?

You can use the SELECT DISTINCT statement (for example, SELECT DISTINCT Column_Name FROM Table_Name) in the Query Design SQL view to retrieve only the unique records from a specific field.

Can I use the DCount function to count distinct values in Access?

The built-in DCount function counts all non-null records matching specific criteria, but it does not natively filter for distinct values. To count distinct values with DCount, you must apply it against a saved query that already has a DISTINCT clause applied.

Office Quick Solutions