May 16, 2005 at 4:04 am
HI i have a query created, that i'm using to show how many classes a student has missed, i doning this by pullin all records where attended = absent... When this query is opened i also want to show the total number of classes the student has that day( all classes where attended = present and excused also). My problem is that with my SQL code, because i'm using the where clause to pull all with absent, i can't count all the other classes.
So is it possible to alter this code to show the total number classes that day. Maybe i can exculde the count od total classes form the where clause or count them from a different query. I'm really lost here please help
SELECT Attendance.Date, Count(Attendance.Attended) AS CountOfAttended, Student.StudentName, Count(Attendance.ClassID) AS CountOfClassID
FROM Student INNER JOIN (Groups INNER JOIN (Classes INNER JOIN Attendance ON Classes.ClassID = Attendance.ClassID) ON Groups.GroupID = Attendance.GroupID) ON Student.StudentID = Attendance.StudentID
WHERE (((Attendance.Attended)=' Absent'))
GROUP BY Attendance.Date, Student.StudentName, Student.StudentID
HAVING (((Student.StudentID)=[Forms]![Frm]![cboStudent]));
Thanks
Chris Lynch
May 16, 2005 at 10:58 am
Until someone comes up with something smarter...an union should work in the interim...
select * from table where attended = 'absent' and student_id = @id and date = @date
union
select * from table where attended = 'missed' and student_id = @id and date = @date
etc.....
**ASCII stupid question, get a stupid ANSI !!!**
May 16, 2005 at 10:45 pm
Why not just add the Attended Field to the grouping and remove it from the criteria
Should give you something like -
Student ID Date Attended Count
---------- ----- --------- ------
123 16 May 05 Present 4
123 16 May 05 Excused 1
123 16 May 05 Absent 2
May 17, 2005 at 1:02 am
I usually use something like SUM(IIF(Attendance.Attended)=' Absent',1,0)) for this type of query, so you might try:
SELECT Attendance.Date, SUM(IIF(Attendance.Attended)=' Absent',1,0)) AS CountOfAbsent, SUM(IIF(Attendance.Attended)='Present',1,0)) AS CountOfAttended, SUM(IIF(Attendance.Attended)=' Excused',1,0)) AS CountOfExcused, Student.StudentName, Count(Attendance.ClassID) AS CountOfClassID
FROM Student INNER JOIN (Groups INNER JOIN (Classes INNER JOIN Attendance ON Classes.ClassID = Attendance.ClassID) ON Groups.GroupID = Attendance.GroupID) ON Student.StudentID = Attendance.StudentID
GROUP BY Attendance.Date, Student.StudentName, Student.StudentID
HAVING (((Student.StudentID)=[Forms]![Frm]![cboStudent]));
President
Schroth Systems Consulting, Inc.
May 17, 2005 at 2:05 am
Use inline subqueries
SELECT Student.STUDENTID, (select count(classid) from attendance where studentid=student.studentid) AS Classes,
(select count(classid) from attendance where studentid=student.studentid and attended="Present") AS Present,
(select count(classid) from attendance where studentid=student.studentid and attended="Absent") AS Absent,
(select count(classid) from attendance where studentid=student.studentid and attended="Excused") AS Excused,
(select date from attendance where studentid=student.studentid group by date) AS [Date]
FROM Student
WHERE (((Student.STUDENTID)=[Forms]![Frm]![cboStudent]));
HTH Richard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply