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(*)


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
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:  Microsoft Access TransferSpreadsheet Function with Ms Excel Examples

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

  • microsoft access count distinct
  • ms access count distinct
  • access distinct count
  • access count distinct function
  • distinct function in access
  • ms access count distinct values
  • count distinct values access
  • distinct count in access design view
  • counting distinct values in access
  • count distinct in access

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

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to

Must read×