Hi All
Before putting my problem out there, you n eed to be aware that I am working in a very different (technically challenged) environment which does not necessarily lend itself to automation in the 'ideal' world scenario.
Hence the following (partly manual) process.
In my Stu_Student table, a field (ID_Att_Code) stores the most recent attendance status for a given student.
As expected, we need to to record attendance each day.
On the ground, the teacher get a PRINTED class list [or subject list which might comprise multiple classes]
This list reflects the last known Attendance code for each student (I.E. Whatever code was assigned at the last roll call).
If the code changes for the student, (eg The student not in class when they should be) then the teacher manually (in ink) changes the code on the page.
That page is then sent to the Admin Officer (AO) to record the attendance.
In order to make life a little easier for this person, I have created a 'bulk update' form.
The problem is that the parameters change, depending on which report is used (Class list/Subject list)
So, The AO might choose to filter by Class name, and/or Year level and/or Attendance code.
The following is the underlying query that the form is based on. But its to complect to be calculated.
Do you know of a better way to handle this situation?
Before putting my problem out there, you n eed to be aware that I am working in a very different (technically challenged) environment which does not necessarily lend itself to automation in the 'ideal' world scenario.
Hence the following (partly manual) process.
In my Stu_Student table, a field (ID_Att_Code) stores the most recent attendance status for a given student.
As expected, we need to to record attendance each day.
On the ground, the teacher get a PRINTED class list [or subject list which might comprise multiple classes]
This list reflects the last known Attendance code for each student (I.E. Whatever code was assigned at the last roll call).
If the code changes for the student, (eg The student not in class when they should be) then the teacher manually (in ink) changes the code on the page.
That page is then sent to the Admin Officer (AO) to record the attendance.
In order to make life a little easier for this person, I have created a 'bulk update' form.
The problem is that the parameters change, depending on which report is used (Class list/Subject list)
So, The AO might choose to filter by Class name, and/or Year level and/or Attendance code.
The following is the underlying query that the form is based on. But its to complect to be calculated.
Code:
SELECT
dbo_STU_Student.ID_Student,
dbo_STU_Student.ID_Campus,
dbo_STU_Student.FORM_Name,
dbo_STU_Student.ID_Att_Code,
dbo_STU_Student.ID_YrLevel,
dbo_STU_Student.FCE, 'Flag that indicates Future, Current or Ex Student
TMP_ATT_History_for_Date.ATT_Date, 'I create a temp table to check if attendance has already been recorded for the student/date
IIf(IsNull([Sq]),1,[sq]) AS Seq 'If applicable - This counts the number if times that attendance has been recorded for this student/date
FROM dbo_STU_Student LEFT JOIN TMP_ATT_History_for_Date ON dbo_STU_Student.ID_Student = TMP_ATT_History_for_Date.ID_Student
'This where I am having trouble
WHERE (((dbo_STU_Student.FORM_Name)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_FormID]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_FormID]")) AND ((dbo_STU_Student.FCE)="C"))
OR (((dbo_STU_Student.ID_Att_Code)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_AttCode]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_AttCode]")) AND ((dbo_STU_Student.FCE)="C"))
OR (((dbo_STU_Student.ID_YrLevel)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_YrLevel]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_YrLevel]")) AND ((dbo_STU_Student.FCE)="C"))
'End of trouble
ORDER BY dbo_STU_Student.ID_Campus,
dbo_STU_Student.ID_FORMID,
Trim([NAME_Surname]) & ", " & Trim([NAME_First]);
Do you know of a better way to handle this situation?