Question Counting

[ijaz196]

I have a table (Student Fine) that contains the following fields:

Studend_ID
Date
Late Commer (Yes / No)
Shoe not polished (Yes / No)
Uniform not clean (Yes / No)
Uniform not Ironed (Yes / No)
Absent (Yes / No)

At the end of the month, I want to count the total number of Late Commer students, total students did not polish the shoes, Total students do not clean the uniform, etc

I am using the expression
=DCount("Student_ID","Student Fine","[Late Commer]= True")
In the result, all students counted, whereas I want to count specific students
can anyone help me?

I shall be grateful

 

[theDBguy]

Hi. My idea is to change your table structure to something like:


tblStudents
StudentID, pk


tblInfractions
InfractionID, pk


tblStudentInfractions
StudentInfractionID, pk
StudentID, fk
InfractionID, fk


You can then easily create a query to count or total each student's infraction.

 

[arnelgp]

you can use Union Query:

select "Late Commers" As Violation, 
          Count(Iif([Late Commer (Yes/No)], 1, Null) As [Total Violators] from [Student Fine]
UNION
select "Shoe not polished" As Violation, 
          Count(Iif([Shoe not polished (Yes/No)], 1, Null) As [Total Violators] from [Student Fine] 
UNION
select "Uniform not clean" As Violation, 
          Count(Iif([Uniform not clean (Yes/No)], 1, Null) As [Total Violators] from [Student Fine] 
UNION
select "Uniform not Ironed" As Violation, 
          Count(Iif([Uniform not Ironed (Yes/No)], 1, Null) As [Total Violators] from [Student Fine] 
UNION
select "Absent" As Violation, 
          Count(Iif([Absent (Yes/No)], 1, Null) As [Total Violators] from [Student Fine]

 

[ijaz196]

Thanks a lot all of you for guiding me. I hope you all guide me also in the future.
Thanks again all of you.