Convert from SQL to access

[thecrew]

I need help converting this statement to work with access 2000

SELECT COUNT(DISTINCT s.supercatID) AS num FROM supercat s WHERE
s.supercatID IN (SELECT p.supercatID from Products p WHERE p.outlet=1)

[Rich P]

>>
SELECT COUNT(DISTINCT s.supercatID) AS num FROM supercat s WHERE
s.supercatID IN (SELECT p.supercatID from Products p WHERE p.outlet=1)
<<
Select Count(t.supercatID) As num From (Select s.supercatID From
supercat s Where s.supercat In (Select p.supercatID from Products p
Where p.outlet = 1) Group by s.supercatID) t

Note: I tried a similar query to this without the alias'es and got the
same results (Access2002 - should work the same in Access 2000). Also,
I try to avoid the Distinct keyword. Group By is more reliable.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[thecrew]

I don't think access 2000 allows for select inside of other select.

From what I understand I need help converting this to a join statement

[Lyle Fairfield]

thecrew wrote:

I don't think access 2000 allows for select inside of other select.

From what I understand I need help converting this to a join statement

Access has nothing to do with this. JET does allow one SELECT inside
another SELECT, (provided the syntax is correct) but not multiple nestings.

If you had quoted a bit of the post which you are referencing we might
have some idea of what you are talking about.

[Alan Webb]

Lyle,
AFAIK, the limit is three-deep. Beyond that Jet chokes. Rich P's
suggestion should work. I use subqueries a lot since getting educated about
them. Very useful thing, subqueries.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Lyle Fairfield" <ff*******@yahoo.com> wrote in message
news:Io****************@read1.cgocable.net...

thecrew wrote:

I don't think access 2000 allows for select inside of other select.

From what I understand I need help converting this to a join statement

Access has nothing to do with this. JET does allow one SELECT inside
another SELECT, (provided the syntax is correct) but not multiple
nestings.

If you had quoted a bit of the post which you are referencing we might
have some idea of what you are talking about.