Query to Calculate percentages of specific records in a table

[AccessNoob67]

Hello all,

Not an Access expert, so please bear with me. I have a table that contains student names, class names, and whether or not the student was on time, tardy or a no show for a given month.

I'm able to get a percentage of on time, tardy and no shows for the entire month without a problem using SELECT Count(*)/(SELECT COUNT(*) FROM [class])*100 AS [Tardy Percent] FROM class WHERE (((class.tardy)=1));

What I want to do is get a percentage for each of these items by professor, but I can't for the life of me figure it out. I add the table with all the professor information to the query and create a relationship between the tables. When I add the professor name to the query I get the following error: Your query does not include the specified expression "Professor" as part of an aggregate function.

Any thoughts?

TIA

 

[plog]

Your query does not include the specified expression "Professor" as part of an aggregate function.

Every field in the SELECT must either appear in the GROUP BY or have an aggregate function (MAX, MIN, COUNT, AVG, etc.) applied to it in the SELECT. Your SELECT didn't do this for at least 1 field. Since you want data by Professor it definitely needs to be in the GROUP BY and not have a function around it.

For the query you want I would need your table structure--name of it and fields.

 

[AccessNoob67]

Every field in the SELECT must either appear in the GROUP BY or have an aggregate function (MAX, MIN, COUNT, AVG, etc.) applied to it in the SELECT. Your SELECT didn't do this for at least 1 field. Since you want data by Professor it definitely needs to be in the GROUP BY and not have a function around it.

For the query you want I would need your table structure--name of it and fields.

Table 1:Students
Fields:
StudentID
ClassName
StudentName
StudentEmail
OnTime (0/1)
Tardy (0/1)
Missed (0/1)

Table 2: Professors
Fields:
ProfessorID
StudentEmail

Joined on the student email. And FYI...I was handed this thing and asked to get some reporting out of it. I'm not a DB guy so this is pretty foreign stuff to me. I'm trying to muddle along.

 

[The_Doc_Man]

I believe there is a structural error in what you showed us. Your "Professors" table can't be right if the professors specialize at all. To get student information from a table showing class attendance and related factors but based on their professors, you would need ClassName and ProfessorID, not StudentEmail and ProfessorID. Your table2 cannot tie back to a single "class record" (which table1 provides). Then there is the problem that you claim this table is for a given month, but there is no date in either table. So how do you know a record IS for a given month and not some OTHER given month? Or were you going to purge the tables on a monthly basis anyway?

 

[Pat Hartman]

If you post your tables, we can be more specific. However, using the QBE to create the new query will help you to get it right. In the new query, you need to join the class attendance table to the professors table and select the professor and class and year/month to group by. The QBE will create a simpler query than what you originally created since a sub select is not necessary to perform this operation. You also will need criteria for year/month if you just want to see one specific month.

 

[mike60smart]

Table 1:Students
Fields:
StudentID
ClassName
StudentName
StudentEmail
OnTime (0/1)
Tardy (0/1)
Missed (0/1)

Table 2: Professors
Fields:
ProfessorID
StudentEmail

Joined on the student email. And FYI...I was handed this thing and asked to get some reporting out of it. I'm not a DB guy so this is pretty foreign stuff to me. I'm trying to muddle along.

This table is wrong:-

Table 1:Students
Fields:
StudentID
ClassName
StudentName
StudentEmail
OnTime (0/1)
Tardy (0/1)
Missed (0/1)

You should have a separate tables as follows for StudentsAttendance and AttendanceTypes with a structure as follows:-

tblStudentsAttendance
-StudentAttendanceID - PK
-StudentID
-Attendance
-AttendanceTypeID

tblAttendanceTypes
-AttendanceTypeID - PK
-AttendanceType (This will be the list of attendance Types - OnTime, Tardy & Missed