Query help

[mapat]

Hello,

If I have a table with duplicate fields (studentID):
studentID Year
a 2005
a 2006
a 2006
b 2006
b 2007
b 2007

Is there query that would give me the count of students by year? Note that the duplicates have to be excluded.
So the resulting table would be:
Year studentCount
2005 1
2006 2 (and NOT 3)
2007 1 (and NOT 2)

Thank you very much

 

[Brianwarnock]

You will need to use a sub query such as

Select t.yr, count(t.studentid) as countstid
FROM (SELECT DISTINCT Table1.yr, Table1.studentid
FROM Table1) as t
Group By t.yr;

I wouldn't use the ACcess fubction Year as a field name.

Brian