Count distinct entires

[pickslides]

Hi all,

Please see DB attached.

I wish to count how many students are in each subject by year level

Output for this table would be a crosstab (select query is fine)

Subject / Year Level .... 7 ..... 8
Math ...... 8 ....... 5
Sport ...... 5 ......... 3



Thanks, Q

 

[arnelgp]

see Query1

 

[arnelgp]

oopps, see Query3 instead.

 

[pickslides]

oopps, see Query3 instead.

Thanks, this is great.

Can this be done in one select query? Let's say the crosstab isn't needed.

Q

 

[arnelgp]

crosstab is the simplest way.
but you can use a simple select query and you
will need to modify everytime there is new Year Level:

SELECT Query2.Subject, Count(IIf([year level]='7',1,Null)) AS 7, Count(IIf([year level]='8',1,Null)) AS 8
FROM Query2
GROUP BY Query2.Subject;

 

[pickslides]

So always pigging backing queries?

If I had my subjects named 7Math , 8Math, 7Sport, 8Sport and so on, then I could get distinct count with one select query?

 

[jdraw]

COUNT(DISTINCT column_name) is not supported in Microsoft Access databases

 

[Pat Hartman]

Select Subject, Semester, Count(*) as Students From tblRoster
Group by Subject, Semester;

That will give you a list of all subjects by year with a count. Add criteria if you want only a specific year.

 

[June7]

The field name is Year Level, not Semester.

However, because there are multiple tests for a student in the same subject and year, student will be counted more than once for that subject and year. If you want to count each student only once for each subject and year, need nested subquery.

SELECT [Year Level], Subject, Count(*) AS StudentCount
FROM (SELECT DISTINCT [Student ID], [Year Level], Subject FROM Student_Results)
GROUP BY [Year Level], Subject;

Strongly advise not to use space in naming convention.

 

[Pat Hartman]

If you don't have a table that defines each subject/Year Level for a student, your schema is incorrect.