Microsoft Access Count Distinct Values Function

Ms Access Count Distinct function in normal SQL language is the function to determine the number of records of different non-NULL values, while Count function return the number of records regardless there are duplicates or not. However, in Access, the Count Distinct function is not actually existed. If you tried to use the function in SQL Query view, it will display the error message instead. But it does not mean you are not able to count the different value records in Access. You cannot use the Count Distinct syntax directly, it is more challenging and needs a little trick in Access.

Syntax for Tricky “Count Distinct” function in Access:
SELECT count(*)
FROM
(SELECT DISTINCT Name FROM table1)

Example:

Ms Access Count Distinct-1

Ms Access Count Distinct-1

With sample table above, we are going to count the Total of Subject, ignoring the Grade. Normally in SQL, you only have to write:
SELECT Count(Distinct Subject) AS Total FROM tblSubject
However, by using Access, you may follow these steps:
1. In Query Design, switch to SQL view.
2. In SQL view window, write the code:
SELECT count(*) AS Total
FROM (SELECT DISTINCT Subject FROM tblSubject)
3. Click Run. If done right, the result will be returned: 5.

Aside for the syntax above, you can modify it or define your own syntax. Sometimes, subquery may be persisted when you modify it (READ more: Ms Access Subquery). Just choose the most appropriate, convenient code that matches with the results.

Read Also:  How to Solve Unrecognized Database Format Access 2013

Here is the other example without Select Distinct:

Ms Access Count Distinct-2

Ms Access Count Distinct-2

The Student table above contains the Name, Subject, and their Grades. We’re going to make a query to count the taken subjects for each student.
1. In Query Design, switch to SQL view.
2. In SQL view window, write the code:
SELECT StudentName, Count(Subject) AS FinishedSubject, AVG(Grade) AS FinalGrade
FROM Students
GROUP BY StudentName;
3. Click Run and see the Results:

Ms Access Count Distinct-3

Ms Access Count Distinct-3

NOTE: If the data is on a server or applications that supports Count (Distinct) function, you might be able to use a pass-through query to retrieve the results. For more information about pass-through query, Read: Ms Access Pass Through Query

Related Access Database

  • access count distinct
  • access 2016 count distinct
  • microsoft access 2016 queries with expression builder function pdf
  • access count distinct values
  • access distinct
  • access distinct count
  • ms access count unique values
  • access: UNIQUE VALUE EXPRESSION BUILDER
  • count distinct in ms access
  • count distinct query builder access functions


Access Database Tags: #access 2013 count distinct #access count unique values in column #count distinct access query design #count distinct in access query builder #count unique values in access report #dcount access #ms access 2010 syntax distinct count query #ms access count distinct #ms access count distinct expression builder #ms access count distinct values

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top