count groups of entries

[Gerhard]

Hello,

I have a database table like this:

Proj_Number Name Task
10 Jack 1
20 Jack 1
30 Jack 1
30 Jack 2
35 Jack 1
10 John 3
25 John 3
45 Ken 4
....

I now would like to have a SQL query which gives me the number of
projects per person, like this:

Jack 4
John 2
Ken 1

The tricky part is that there might be lines with the same project
number but different task number (as you see for Proj_number 30). I
want only the number of projects that a person works on, not regarding
on how many tasks he/she works.
How can I do this?

Thanks,
Gerhard.

 

[Van T. Dinh]

Create 2 Queries:

Query1:

SELECT DISTINCT Table1.[Name], Table1.[Proj_Number]
FROM Table1

Query2:

SELECT Query1.[Name], Count(Query1.[Proj_Number]) AS ProjCount
FROM Query1
GROUP BY [Name]

Second Query should give what you want.

You can combine 2 Queries into one.