Question Counting (1 Viewer)

ijaz196

Registered User.
Local time
Tomorrow, 02:19
Joined
Dec 29, 2017
Messages
22
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

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:19
Joined
Feb 19, 2002
Messages
42,976
Except that the expression doesn't take date into consideration, there isn't anything wrong with it.

Your design, however, resembles a spreadsheet rather than a table in a relational database. To do this properly, you need THREE tables.
tblStudents:
StudentID
LastName
FirstName
....
tblIssues:
IssueID
IssueName (Tardy, Shoe not polished, uniform not clean, ...)

tblStudentIssues:
StudentID
IssueDate
IssueID

The first table is obvious. It is all the students. The second is a list of all the issues you want to monitor. When you put the issues in a column, you have to change all your forms and all your queries in order to add a new issue. What if tomorrow, you wanted to track uncombed hair? Using the normalized schema I recommend, you would simply add a new row to tblIssues and then start recording incidents.
The third table is a junction between students and issues and is the way we create a many-many relationship. The tblStudentIssues contains the date (Date is a reserved word so it should never be used as a column name) and the IssueID.

To make a report, you join tblStudents --> tblStudentIssues -->tblIssues and select the Student name and the issue name and add a count(*) field. The query can (probably should) take criteria so you can enter a date range to select only the issues between two dates.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:19
Joined
May 7, 2009
Messages
19,169
you can use Union Query:
Code:
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]
 
Last edited:

ijaz196

Registered User.
Local time
Tomorrow, 02:19
Joined
Dec 29, 2017
Messages
22
Thanks a lot all of you for guiding me. I hope you all guide me also in the future.
Thanks again all of you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:19
Joined
Feb 19, 2002
Messages
42,976
Two of the three suggestions do not consider a date range argument. Without it, whether you normalize the data or not, you will not get a count for the month.
 

Users who are viewing this thread

Top Bottom