Solved Getting Count of Present, On Leave and Absent Students (1 Viewer)

abdullahkhan

Member
Local time
Today, 16:14
Joined
Apr 14, 2020
Messages
268
Hello,

I want to use a subform with master and child link as StudentID and the purpose is to get student attendance status in each month. I'm using following query but it is not working as required. I don't know where is wrong. Can anybody guide me how can I get Attendance status of a student in each month. Attendance status are 1,2, and 3 for present, absent and on leave repsectively.

SQL:
SELECT tblAttendance.StudentID, Month([attDate]) AS expAttMonth, Count([attStatus]=1) AS expPresent, Count([attStatus]=2) AS expAbsent, Count([attStatus]=3) AS expLeave
FROM tblAttendance
GROUP BY tblAttendance.StudentID, Month([attDate]);

Best Regards,
Abdullah
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:14
Joined
Sep 21, 2011
Messages
7,790
I would have thought you would need an If statement for the count?
Code:
SELECT Count(IIf([attstatus]=1,1,0)) AS expPresent
 

abdullahkhan

Member
Local time
Today, 16:14
Joined
Apr 14, 2020
Messages
268
Thanks for reply.
Can you upload a zipped copy of the database?
Please find attached db. Query1 is not showing corresponding results for users e.g. right now I have selected user 2. This query will serve as source object for the subform.
I would have thought you would need an If statement for the count?
Code:
SELECT Count(IIf([attstatus]=1,1,0)) AS expPresent
I try to use this code but it is giving syntax error and is not letting me save or view the query in other mode. I used design mode and then defined the expPresent as expPresent: (Select Count(IIF([attStatus]=1,1,0))). I did try to change it somewhat but either it is giving syntax error or showing zero records in datasheet view of query.
 

Attachments

  • StudentAttendance.accdb
    800 KB · Views: 25

Gasman

Enthusiastic Amateur
Local time
Today, 11:14
Joined
Sep 21, 2011
Messages
7,790
Paste this into the sql view, then look at design view.
Code:
SELECT Month([attDate]) AS expAttMonth, Sum(IIf([attStatus]=1,1,0)) AS expPresent, Sum(IIf([attStatus]=2,1,0)) AS expAbsent, Sum(IIf([attStatus]=3,1,0)) AS expLeave
FROM tblAttendance
WHERE (((tblAttendance.StudentID)=2))
GROUP BY Month([attDate]);
 

abdullahkhan

Member
Local time
Today, 16:14
Joined
Apr 14, 2020
Messages
268
Paste this into the sql view, then look at design view.
Code:
SELECT Month([attDate]) AS expAttMonth, Sum(IIf([attStatus]=1,1,0)) AS expPresent, Sum(IIf([attStatus]=2,1,0)) AS expAbsent, Sum(IIf([attStatus]=3,1,0)) AS expLeave
FROM tblAttendance
WHERE (((tblAttendance.StudentID)=2))
GROUP BY Month([attDate]);
Thank you very much @Gasman, it worked perfectly. First I thought, writing sum in the query was a typo which it wasn't. Using sum to get no of counts was very smart. Bundler of thanks.
 

abdullahkhan

Member
Local time
Today, 16:14
Joined
Apr 14, 2020
Messages
268
Hello again,

With the same data, for inserting into report, I want to make a subreport in the format shown below in picture:

Screenshot 2021-01-27 130748.png


where columns heading 1,2,3,4... are month no upto 12. I can use cross tab query to generate this data for present, absent and leave (attStatus = 1,2,3 respectively) row heading using the following query but how can I get Total Classes row heading for total no of classes in month which is equal sum of present, absent and leave for a particular month.
SQL:
TRANSFORM Count(tblAttendance.AttStatus) AS CountOfAttStatus
SELECT Year([AttDate]) AS attYear, tblAttendance.AttStatus
FROM tblAttendance
WHERE (((tblAttendance.StudentID)=2))
GROUP BY tblAttendance.StudentID, Year([AttDate]), tblAttendance.AttStatus
PIVOT Month([attdate]);

How can I make a query to get data so that I can make it recordsource of subreport.

Thanks in advance.

Best Regards,
Abdullah
 
Last edited:

Users who are viewing this thread

Top Bottom