I need help with Access 2007

Souperfro · 03-12-2009, 01:21 PM

I'm trying to make 2 different counts in one single query.

I'm not too familiar with SQL, I've actually just been using the design view to make past queries.

Here is what I have atm -

Code:

SELECT Teacher.TeacherName, Count(Assignments.AssignmentNum) AS CountOfAssignmentNum, Count(Assignments.AssignmentNum) AS CountOfAssignmentNum1
FROM (Teacher INNER JOIN Tests ON Teacher.[TeacherNum] = Tests.[TeacherNum]) INNER JOIN Assignments ON Teacher.[TeacherNum] = Assignments.[TeacherNum]
GROUP BY Teacher.TeacherName
ORDER BY Teacher.TeacherName;

What happens is that when I run the query, it counts up every assignment AND test for every teacher and lists them (together) in both the assignment count and test count columns.

I want it to be separate, and while I've googled this problem and found solutions, I can't make out what I need to change in the SQL to fix my issue. Thanks.

03-12-2009, 01:21 PM	#1 (permalink)
Souperfro Registered User Join Date: Mar 2009 Posts: 1 OS: XP	I need help with Access 2007 I'm trying to make 2 different counts in one single query. I'm not too familiar with SQL, I've actually just been using the design view to make past queries. Here is what I have atm - Code: SELECT Teacher.TeacherName, Count(Assignments.AssignmentNum) AS CountOfAssignmentNum, Count(Assignments.AssignmentNum) AS CountOfAssignmentNum1 FROM (Teacher INNER JOIN Tests ON Teacher.[TeacherNum] = Tests.[TeacherNum]) INNER JOIN Assignments ON Teacher.[TeacherNum] = Assignments.[TeacherNum] GROUP BY Teacher.TeacherName ORDER BY Teacher.TeacherName; What happens is that when I run the query, it counts up every assignment AND test for every teacher and lists them (together) in both the assignment count and test count columns. I want it to be separate, and while I've googled this problem and found solutions, I can't make out what I need to change in the SQL to fix my issue. Thanks. Last edited by Souperfro; 03-12-2009 at 01:24 PM.