Need some help to write a simple query

[UGrad]

Hi,
I am trying to find a way to write a query. I think it's easy, but I just don't know how to do it.
I have a table called "table1" looks like this:
EmployeeID CarID
-------------------
1 1
2 2
3 3
4 4
5 5
1 6
3 7
4 8
6 9
3 10
2 11
2 12

All I want is write a query to return the number of employee who has more than 3 cars.
The query I wrote is:

select count(Distinct EmployeeID) as num from table1 t where (select count(CarID) from table1 where t.EmployeeID = table1.EmployeeID) > 3

Is there another way to write query return the same result?

I am thinking about something like:

select count(EmployeeID) as num from table1 group by EmployeeID having (count(CarID)>3)

But it's not returning the correct value.

 

[PRMiller]

Is CarID the car identifier, or the actual number of cars the employee possesses?

 

[UGrad]

CarID is car identifier.

 

[UGrad]

So For EmployeeID 1, he has 2 cars.
For EmployeeID 6, he has 1 car.

 

[PHV]

You may try this:
SELECT Count(*) As num
FROM (SELECT DISTINCT t.EmployeeID FROM Table1 t INNER JOIN
(SELECT EmployeeID FROM Table1 GROUP BY EmployeeID HAVING Count(*) > 3
) g ON t.EmployeeID = g.EmployeeID);
As I'm not sure JetSQL admits the Count(Distinct) aggregate function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UGrad]

Thanks, PHV.
I wrote my query in SQL and didn't know Access doesn't support count(distinct).