Nested aggregate function

[hoos1]

I am trying to write a SQL statement that returns a record with the minimum count value for a table<br><br>Select student_no, count(*)<br>&nbsp;from class<br>&nbsp;group by student_no<br>&nbsp;having count(*) = <br><br>(Select min(count(*))<br>&nbsp;&nbsp;from class<br>&nbsp;&nbsp;group by student_no);<br><br>Access doesn't seem to like the aggregate nested within the aggregate but I think this is valid SQL in other systems.&nbsp;&nbsp;Any ideas on how to do this?&nbsp;&nbsp;Thanks.<br><br>

 

[PaulF]

make a new query that performs what you want, select SQL view, copy and paste the results.<br><br>PaulF

 

[hoos1]

Could you be a little more specific.&nbsp;&nbsp;If I could write a query that performs what I want, I would have written it.&nbsp;&nbsp;Where does copying and pasting come in.<br><br>BTW: My table consists of a two part primary key - student_no and class_no.&nbsp;&nbsp;I'm trying to find the student that has taken the least number of classes.

 

[PaulF]

This statement was made using the Query Builder in Access with the Totals option turned on.&nbsp;&nbsp;I made a table named tblStudents which contained fields Student_No and Class_No.&nbsp;&nbsp;Then I entered several records.&nbsp;&nbsp;The results of the below SQL statement has the student_No with the lowest count of classes listed first.&nbsp;&nbsp;If you want the student with the most classes, you'd&nbsp;&nbsp;add DESC in the ORDER BY statement before the semi-colon<br><br><br>SELECT tblStudents.student_no, Count(tblStudents.class_no) AS [Classes Taken]<br>FROM tblStudents<br>GROUP BY tblStudents.student_no<br>ORDER BY Count(tblStudents.class_no);<br><br><br>After I ran the query to test the results, I selected SQL View in the Query Builder, and copied the statement.<br><br>PaulF

 

[hoos1]

Thanks for your help, but do you have any idea how to only return the student number(s) that have the minimum count value. I'd rather not display all of the count records if possible.&nbsp;&nbsp;