I agree with Ajax's solution --- there is no Count Distinct in Access.
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name;
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
You can create a user defined function
Code:
' ----------------------------------------------------------------
' Procedure Name: CountDistinct
' Purpose: To get Count of Distinct Values in a given table
' Procedure Kind: Function
' Procedure Access: Public
' Parameter YourField (String): Field to be Counted
' Parameter YourTable (String): Table with Field
' Return Type: Integer
' Author: Jack
' Date: 21-Feb-21
' ----------------------------------------------------------------
Function fCountDistinct(YourField As String, YourTable As String) As Integer
10 On Error GoTo fCountDistinct_Error
Dim SQL As String
Dim rst As DAO.Recordset
20 SQL = "SELECT Count(" & YourField & ") as DistCount" _
& " from (select distinct " & YourField _
& " from " & YourTable & ");"
30 Set rst = CurrentDb.OpenRecordset(SQL)
40 fCountDistinct = rst!DistCount 'return the Count of Distinct values
50 rst.Close
60 On Error GoTo 0
fCountDistinct_Exit:
70 Exit Function
fCountDistinct_Error:
80 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fCountDistinct, line " & Erl & "."
90 GoTo fCountDistinct_Exit
End Function
Sample query:
SELECT fCountDistinct("City","CustomerA") AS demo
FROM CustomerA
GROUP BY fCountDistinct("City","CustomerA");