Counting entries in a query

[jtamaro]

I want to display the Student_ID, Section_ID and Enroll_Date from the Enrollment table for the first 10 students to have enrolled in February 2003 (there were actually 25 students that enrolled during 2/2003).

I can select the students that enrolled in 2/2003 but the grouping does not allow me to filter by count. Here's the SQL that is generated:

SELECT Year(Enrollment!Enroll_Date) AS Date_Enroll, Enrollment.Student_Id,
Count(Enrollment.Section_Id) AS CountOfSection_Id

FROM Enrollment

GROUP BY Year(Enrollment!Enroll_Date), Enrollment.Student_Id

HAVING (((Year(Enrollment!Enroll_Date))=2003)
AND ((Count(Enrollment.Section_Id))<11));

Can anyone help?

John

 

[BigJimSlade]

hmmmm, jt!

This sounds like a class project, but Big Jim is always happy to help someone squeeze by....

First off, I think you are trying to use COUNT the wrong way. COUNT returns an aggregate count, or total, number of records in each grouping.

To determine who the first 10 to enroll were, you need some other indicator to go by. For example, an AUTONUMBER counter would be great. Also, if the DATE field [Enroll_Date] you described shows hour and minute information, you could base it off of the minute/hour/day someone was signed into the database.

Although......maybe you are just looking for a way to pull the Top 10 the best way the database can as it is set up. Try something like this:

SELECT TOP 10 Student_ID AS TenFirstStudents
FROM [Enrollment Table]
WHERE Year(Enroll_Date) = 2003
AND Month(Enroll_Date) = 2
ORDER BY Enroll_Date

"TOP" works wonders, check it out in the help file.

Just a few thoughts!

Big Jim