Count vs. Function for Attendance Report

[Surfette]

Greetings!

I'm trying to make an Attendance Report for my students. I want the report to show each student and how many times they've been "Present", "Late" or "Tardy" in a month and in one year.

I've set up my db with two tables.

Table 1 - contains Student ID and Student Names
Table 2 - contains Attendance ID, course id, student id and the Status ("Present", "Late" or "Absent")

I used the sum queries and it doesn't do the count function properly. This is a copy of my query.

Thanks
Surfette

 

[Jon K]

Since the Status field contains 3 statuses, you can't use count on the field to get the count of each status. Instead, you have to sum 3 expressions.

See the query in the attachment.
.

 

[EMP]

When the Status field is included in the Group By clause, instead of returning one record per student per month, a student may have one to three records returned per month in the results.

^

 

[Surfette]

Since the Status field contains 3 statuses, you can't use count on the field to get the count of each status. Instead, you have to sum 3 expressions.

See the query in the attachment.
.

Hey Jon K,

Thanks for the help! It worked like a charm!

Many many thanks!!!!

Surfette