Link to home
Start Free TrialLog in
Avatar of nocturn4l

asked on

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

Microsoft Access 2003

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
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
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nocturn4l


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