Can this be done in one query?

[Lars Brownies]

I have this table:
ID_car ID_category
1 10
2 10
2 20
2 30
3 20

I need to pull out the ID numbers which have values for all categories. So
in this example the result should be ID number 2. I can easily do this when
I would know the number of categories beforehand, in this case 3:

SELECT ID_carFROM tblTest GROUP BY ID_car HAVING (((Count(ID_category))=3));

How can I tackle this when I don't know the number of categories beforehand?
Can I do this in one query?

Thanks,

Lars

 

[Tom van Stiphout]

On Tue, 17 Nov 2009 08:37:04 +0100, "Lars Brownies"

So get that count dynamically. Replace "3" with:
(select count(*) from tblCategories)

-Tom.
Microsoft Access MVP

 

[Lars Brownies]

In this case it is possible that not all Categories of the Categories table
are in tblTest, which is a temporary selection table. But I got the result
using your suggestion, ending up in a three-in-one query:

SELECT ID_car
FROM tblTest
GROUP BY ID_car
HAVING (((Count(ID_category))=(SELECT Count(*) AS N
FROM [SELECT DISTINCT ID_category FROM tblTest]. AS T)));

Thanks!
Lars