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 Update Access Database from Ms Excel

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 disinct
  • access count distinct function
  • Access course attendance templte
  • microsoft access count distinct
  • ms access count distinct values


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×

Top