how to count records in a query

[Guest]

i have 2 tables linked one that has company info in it and one with student
class info
i have a query that pulls together this info
what i am trying to do is count the number of students with this company one
time not each time the student is listed with the company.

example:

student # company # companyname class name
22556 2005-003 lane word
22556 2005-003 lane access
33995 2005-003 lane word
33995 2005-003 lane access
44556 2005-003 lane excel
55261 2005-003 lane excel

the results i want from my query is
student # company # company name
(# of students) 4 2005-003 lane

by using the "count" in the total row of the query in design view it counts
every record not every individual student.
please help!!!!!!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Unfortunately, JET doesn't have a COUNT(DISTINCT...) function, so we
have to create a subquery that just counts distinct values in one
column.

SELECT CompanyName, CompanyNo,
Count(SELECT DISTINCT student_no FROM table
WHERE CompanyNo = T.CompanyNo) As Students
FROM table as t
WHERE < criteria >

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkyeEIechKqOuFEgEQIs1gCcCEbjA/6oZuCCP/Ufanvxplooi+sAni9p
isIE5DnH68xDcdpymgddBPJr
=DDGp
-----END PGP SIGNATURE-----