FYI on Count Distinct

[Steve Dassin]

Here's some more doddling on simulating count distinct in Access.
The following table lists states, cities within states and
towns within cities. Fields pkey and pkeytext can each
be used for a primary key.
Table tblstate
pkey pkeytext state city town
1 XA CA A h
2 AC CA A h
5 YR CA C j
6 DC CA C j
7 HD NY G n
8 TS NY G n
9 SG NY H p
10 WH CA D k
11 US NY G q
13 AS FL A r
19 BS CA C m
22 EA CA B h
25 AA FL A s

Distinct count of states:
SELECT "distinct" AS states,
Count(Switch(a.pkey=Dlookup("[pkey]","tblstate","[state]='"&a.state&"'" ),1))
AS [distinct count of states]
FROM tblstate AS a;

states distinct count of states
distinct 3

Distinct count of cities within states:
SELECT a.state,
Count( switch( a.pkey= Dlookup("[pkey]","tblstate","[state]='"&a.state&"'" &
"AND" &"[city]='"&a.city&"'"),1)) AS [distinct count of cities]
FROM tblstate AS a
GROUP BY a.state;

state distinct count of cities
CA 4
FL 1
NY 2

Distinct count of towns within cities and states:
SELECT a.state, a.city,
Count( switch( a.pkeytext=
Dlookup("[pkeytext]","tblstate","[state]='"&a.state&"'" & "AND"
&"[city]='"&a.city&"'" & "AND" & "[town]='"&a.town&"'" ),1))
AS [distinct count of towns]
FROM tblstate AS a
GROUP BY a.state, a.city;

state city distinct count of towns
CA A 1
CA B 1
CA C 2
CA D 1
FL A 2
NY G 2
NY H 1

Distinct count of towns with state:
SELECT a.state,
Count( switch( a.pkeytext=
Dlookup("[pkeytext]","tblstate","[state]='"&a.state&"'" & "AND" &
"[town]='"&a.town&"'" ),1)) AS [distinct count of towns]
FROM tblstate AS a
GROUP BY a.state;

state distinct count of towns
CA 4
FL 2
NY 3

Get it?
Steve Dassin