Count Students Who Attended Class?

[Guest]

Hi:

We have a many to many application and I want a query that counts the number
of students who both registered and then actually attended the class. Those
students who registered and didn't attend will have an [attend_status] such
as "No Show" or "Cancelled". If they actually attend the class their
[Attend_Status] = "Attended".

The two tables are tblClasses and tblLink.

The two important fields in tblLink are Student_ID and [Attend_Status] and
the criteria is [Attend_Status] = "attended"

I have a query that was working, but now it's counting every student who was
originally registered, even if they didn't attend the class. Obviously we
only want to count those students whose [Attend_Status] = "attended"

The query that was working ever so briefly, but now it needs some serious
tweaking.

SELECT tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName,
Count([tbllink].[attend_status]="attended") AS [Students Attended]
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName;

Thanks,
Robert

 

[Gary Walter]

Hi:

We have a many to many application and I want a query that counts the
number
of students who both registered and then actually attended the class.
Those
students who registered and didn't attend will have an [attend_status]
such
as "No Show" or "Cancelled". If they actually attend the class their
[Attend_Status] = "Attended".

The two tables are tblClasses and tblLink.

The two important fields in tblLink are Student_ID and [Attend_Status] and
the criteria is [Attend_Status] = "attended"

I have a query that was working, but now it's counting every student who
was
originally registered, even if they didn't attend the class. Obviously we
only want to count those students whose [Attend_Status] = "attended"

The query that was working ever so briefly, but now it needs some serious
tweaking.

SELECT tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName,
Count([tbllink].[attend_status]="attended") AS [Students Attended]
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName;

Field: [Students Attended] : Abs(attend_status ="attended")
Table:
Total: SUM
Sort:
Show:
Criteria:
or:

 

[Guest]

It's amazing what one can accomplish if they have time to concentrate and do
one thing at a time. This morning I was busy preparing for two classes I was
scheduled to teach while trying to construct my query. This afternoon, I had
some free time so I concentrted solely upon redesigning, the query and much
to my surprise, I came up with a query construction that apparently works.

SELECT tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName, Count(tblLink.StudentID) AS
[Students Attended]
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
WHERE (((tblLink.Attend_Status)="Attended"))
GROUP BY tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName;

Hey Gary, thanks for the suggestion, I'll try it a little later. Why did you
use Abs?

 

[Smartin]

It's amazing what one can accomplish if they have time to concentrate and do
one thing at a time. This morning I was busy preparing for two classes I was
scheduled to teach while trying to construct my query. This afternoon, I had
some free time so I concentrted solely upon redesigning, the query and much
to my surprise, I came up with a query construction that apparently works.

SELECT tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName, Count(tblLink.StudentID) AS
[Students Attended]
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
WHERE (((tblLink.Attend_Status)="Attended"))
GROUP BY tblClasses.ClassNo, tblClasses.Class_Date, tblClasses.Class_Time,
tblClasses.CourseNo, tblClasses.CourseName;

Hey Gary, thanks for the suggestion, I'll try it a little later. Why did you
use Abs?

PMFJI,

Nice going (^:

In Gary's suggestion the expression [attend_status]="attended" returns a
boolean result: true or false. Boolean true is equivalent /-1/. Wrapping
SUM around this would result in a negative number. Sticking Abs around
the boolean result forces it to be positive.