Session Attendance Sheet

[Sickou]

Good day team,
I'm a teacher not an IT person, but i like to do my work using MS Access


my simple work is as follows:
1- I'm teaching multiple grades for example ( Gr10, Gr11, Gr12)
2- I'm teaching multiple subjects for example (Math, Physics, Chemistry)
3- student can be assigned to only one grade, but can attend sessions for multiple subjects, for example Div in Gr11 but can attend Math and English sessions
4- the attendance sheet should contain the fields, (Session_Date, Grade, Subject, Student_Name)

these four fields need to be entered through a Data Entry Form.
till here it seems fine to me, but i have the challenge which is:

in this form, when i select the Grade, and subject, i need to see only the list of students who were registered for this grade and subject.
I believe i missed the correct tables structures and its relationship, and i couldn't understand how to build this and if i need this auto number primary key in any of my tables? please advise

the same was posted at the below link:
https://www.codeproject.com/Questions/5283703/Creating-a-student-attendance-sheet-in-MS-access

[Gicu]

Do not use lookup fields in tables, instead use combo boxes in your form and set their row source as queries that return only the students for the selected grade and subject. There are many examples in the forums, please post back if stuck, ideally with a sample of your db with just a few "dummy" records to show the problem.

Cheers,

[isladogs]

It would be helpful if you posted a screenshot of the relevant table relationships.
Setting this up properly involves quite a complex table structure

This is a many to many relationship - many students who can each be in many classes

In order to filter students in a particular class, you should have at least 3 tables :
1. tblStudents - StudentID, LastName, FirstName etc
2. tblClasses - ClassID, Subject, Teacher etc
3. tblStudentClasses - junction table including ClassID, StudentID etc

Now use a query linking these tables to get a list of students for that class.
You will also need to consider selecting the date and lesson for the selected class/teacher.
For example, you can use a form like this (taken from the Help file for one of my commercial apps, School Data Analyser:



To bring all these items together, I have additional tables such as tblSubjects, tblTeachers, tblTimetable... etc

In case it helps, attached is a PDF help file showing how the lesson attendance feature is used in my app
Hope that helps get you started

[ArviLaanemets]

In addition to Colin's recommendations, this will get even more complex, when you are teaching more than a single year - with students in different grades in every year (or maybe not in some cases), and participating in different classes in different years - or having different classes for different parts of same year, etc.

[isladogs]

Hi Arvil
Yes I agree.
In my app there are also many other related tables used for this feature e.g. attendance mark type, school calendar dates (to match timetable against) etc etc...

This is just one feature of many in my app which has a total of over 320 data tables in a SQL Server BE

[orange]

Further to the responses received so far, you may find this data model from Barry Williams' site helpful. There are other student/class related models on his site.

[mike60smart]

Hi

Are you able to upload a zipped copy of the database with no confidential data?