count wirth zero count query

[inungh]

I would like to have a query to count students activities which
includes zero count like following

Student table

student ID, Student Name
1 John
2 Mary


Activity Table

Activity ID, Activity Name
1 Piano


Student Activity table

Student ID, Activity ID

1 1
1 1

I would like have a result like following

Strudent ID, Activity ID, Activity Count

1 1 2
2 2 0


For some reason my query does not show student 2, if here is no
activity for student 2.


Your help is great appreciated,

 

[RonaldoOneNil]

SELECT Student.[Student ID], [Student Activity].[Activity ID], Count([Student
Activity].[Activity ID]) AS [CountOfActivity ID]
FROM Student LEFT JOIN [Student Activity] ON Student.[Student ID] = [Student
Activity].[Student ID]
GROUP BY Student.[Student ID], [Student Activity].[Activity ID];

 

[inungh]

SELECT Student.[Student ID], [Student Activity].[Activity ID], Count([Student
Activity].[Activity ID]) AS [CountOfActivity ID]
FROM Student LEFT JOIN [Student Activity] ON Student.[Student ID] = [Student
Activity].[Student ID]
GROUP BY Student.[Student ID], [Student Activity].[Activity ID];

I would like to have a query to count students activities which
includes zero count like following

Student table

student ID, Student Name
     1              John
     2              Mary

Activity Table

  Activity ID, Activity Name
      1             Piano

Student Activity table

   Student ID,  Activity ID

       1                  1
       1                   1

I would like have  a result like following

  Strudent ID, Activity ID, Activity Count

      1                  1               2
      2                   2             0

For some reason my query does not show student 2, if here is no
activity for student 2.

Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks for the message,
For some reason, Access does not let me LEFT JOIN, Access let me do
RIGHT JOIN, but it does not give me right result.

I think that I need check my table links.

Thanks again,