This is my Presence table that tracks the students' attendence and absence
I can display the list of attendees on a grid easily
For this I use:
Code:
StrSql = "Select Fname, Lname , " & _
" BirthDate, classe_name, subject from Student_tbl" & _
" left join Presence_tbl on Student_tbl.ID = Presence_tbl .PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
" left join class_tbl on Presence_tbl.class_id = class_tbl.ID " & _
" Where Presence = 1 and date_id = " & Lbl_Date(1).Caption
Set Rs = Cnn.OpenRecordset(StrSql)
However when I want to display the list of attendees with the number of sessions attended for each pupil, I can only get one record displayed on the grid.
Code:
StrSql = "Select Fname, Lname , " & _
" BirthDate, classe_name, subject, sum (presence) as presences from Student_tbl" & _
" left join Presence_tbl on Student_tbl.ID = Presence_tbl .PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
" left join class_tbl on Presence_tbl .class_id = class_tbl.ID " & _
" Where Presence = 1 and date_id = " & Lbl_Date(1).Caption & " GROUP BY Fname"
Set Rs = Cnn.OpenRecordset(StrSql)
I even used
Code:
count (presence) as presences
but doesn't seem to work
thank you
Last edited by newbie2; Mar 24th, 2023 at 01:40 PM.
Since more than one table is involved via joins -
we cannot recommend anything else but:
- post a zip with your (reduced) sqlite-db
- or alternatively prepare a db-fiddle and post a link
The filter presence =1 is in the Where clause outside the left joins, rendering all left joins to inner joins.
put the filter in each on clause
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad