nested sub queries

[Or]

I have a table named phone_numbers with columns named number, client_id,
program_id. number is the primary key.
I want to select the client id's which for each number they have, there is a
different program_id.
In Oracle, i would do something like this:

SELECT outer.client_id
FROM phone_numbers outer
GROUP BY outer.client_id
HAVING COUNT(*) > 1 AND COUNT(*) =
(SELECT COUNT(DISTINCT program_id) FROM phone_numbers WHERE client_id =
outer.client_id)

The problem is that i'm using ACCESS 2003, and ACCESS doesn't support
COUNT(DISTINCT). So i wrote this:

SELECT outer.client_id
FROM phone_numbers outer
GROUP BY outer.client_id
HAVING COUNT(*) > 1 AND COUNT(*) =
(SELECT COUNT(*) FROM (SELECT DISTINCT program_id FROM phone_numbers WHERE
client_id = outer.client_id))

The problem is that ACCESS doesn't recognize the outer.client_id in the most
inner query. Is there a way to fix that other than break the query into two
smaller queries?

Thanks, Or

 

[bcap]

I think this works. It'll probably also be dramatically faster than the
direction you were going - correlated sub-queries are usually a performance
diasaster in Access.

SELECT
PN1.client_id
FROM phone_numbers PN1 LEFT JOIN
phone_numbers PN2 ON (PN1.client_id = PN2.client_id AND
PN1.program_id = PN2.program_id AND
PN1.number <> PN2.number)
GROUP BY PN1.client_id
HAVING Count(PN2.client_id) = 0