Access Jet Engine does not support count(distinct xxx) syntax, which is supported by all major DBMS systems such as SQL Server, Oracle, MySQL, etc.
instead you must do it like this 2 stage query of an inline view inner query fed into the aggregrate count function.
Example:
select count(yourCol) as num_of_distinct_values
from
(
select distinct yourCol from yourTable
)
Here is a triple Nested query using the technique described. This will get all the leavers of a segment who was there 2 months before.
Example:
SELECT count(cnt_cnc) , “cnt_cnc_leavers_30” as cnt_desc
from
(
select DISTINCT [encounters – 1st Prev Month].full_nm as cnt_cnc
FROM [encounters – 1st Prev Month]
WHERE [encounters – 1st Prev Month].full_nm not in
(
select [encounter – current month].full_nm
from [encounter – current month]
)
)
