asked on

Having trouble with the COUNT function in SQL (Microsoft Access)

Microsoft Access 2003

Tables
Teacher = [CourseN, Quarter,TeacherName]
Course = [Course N Quarter , DayTime, Room #]/ Examples of DayTime: M 2:00,
W 4:50, and T 8:00.
Student = [studentName,Course #, Quarter]

^The underlined words are the primary keys

I want to be able to list the student name that took the most courses.. but I'm having trouble coming up with the correct syntax

SELECT A.studentName
FROM Student A
WHERE (SELECT COUNT(CourseN)
FROM Student B
WHERE COUNT(CourseN)) > ......something here

or If I wanted to list every course number and the student's name who has taken the same course twice... I have something like:

SELECT A.studentName
FROM Student AS A
GROUP BY A.studentName
HAVING Count(CourseN)>2;

but that's coming up wrong too..

any help would be appreciated, thanks

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nocturn4l

ASKER

Thanks mwvisa1, those 2 that you wrote both worked just fine. Though I had to remove the AS Crit in the statements as they seem to not be working with them in there.. probably just a MS Access syntax thing

for the 1st one i had to change it to:

SELECT TOP 1 A.studentName, COUNT(A.CourseN)
FROM Student A
GROUP BY A.studentName
ORDER BY A.studentName DESC;

and 2nd one i changed it to:

SELECT A.studentName, A.CourseN, COUNT(A.CourseN)
FROM Student AS A
GROUP BY A.studentName, A.CourseN
HAVING COUNT(A.CourseN) >= 2;

thanks again, it was of much help