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

nocturn4l
nocturn4l used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Hi.

Firstly, you cannot use the result of COUNT(...) in WHERE clause as it is evaluated before the SELECT clause.

For the first query, you want TOP.

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

For the next query, if you want students that took the same course twice, you should probably group by the course name.

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

By the way, note if you are looking for at least twice, then should be >= 2.  If looking for exactly twice, then it should be = 2.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial