
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:

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.
Here is the other example without Select Distinct:

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:

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