Table: Test
Customer Number
5
6
7
8
8
SELECT COUNT(DISTINCT[Customer Number])
FROM Test
Expected Result = 4
Asked
Active
Viewed 229 times
2
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
John Daly
- 201
- 1
- 3
- 8
1 Answers
0
MS Access does not support COUNT(DISTINCT). You can use two aggregations:
select count(*)
from (select distinct [Customer Number]
from Test
) as t;
Note: This counts NULL values whereas COUNT(DISTINCT) does not. You can filter them out in either the subquery or outer query if that is an issue.
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
-
select count(*) from (select distinct [Customer Number] from prod ) as t WHERE RU <> '#N/A' AND [Open Amount] <> 0; If I had to add two where clauses - This is showing as a parameter input on Access – John Daly May 27 '21 at 12:51
-
@JohnDaly . . . You have to add them into the subquery. – Gordon Linoff May 27 '21 at 12:54
-
Working - Thanks Gordon – John Daly May 27 '21 at 13:46