Click here to Skip to main content
15,171,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day team,
i'm a teacher not an IT person, but i like to do my work using MSAccess
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 from the same grade can register and attend for more than one subject for the same grade
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 the the Student_Name field to display only the student regestered for this gared and subject.

What I have tried:

i created four tables,
- Students table, having three columd for student name, grade, and subject, in this table the same student can have multiple records according to the number of subjects he is studying
- Grades table, havingone colomn for the grade,
- Subjects table, having one column for the subject names
but couldnt create the relationship that makes the form run the way i described above
Updated 27-Oct-20 2:27am

Shakshoka1357 27-Oct-20 12:50pm
Thank you Richard
You might have to rejig your database structure. Your Grade table should have two columns, the ID of the grade and the name

GradeID, GradeName

Same with Subject

SubjectID, SubjectName

Now create a Student table

StudentID, StudentName

Now a table to hold which student does which subject and grade, you could call this something like StudentGrade with columns like

StudentGradeID, StudentID, GradeID, SubjectID

All of your ID fields are primary key autonumber fields and the GradeID field in the Grade table will have a relationship with the GradeID field in StudentGrade, the same with StudentID and SubjectID. When populated your tables will look like

StudentID, StudentName
1, John
2, Dave

GradeID, GradeName
1, Gr10
2, Gr11

SubjectID, SubjectName
1, Maths
2, English

So if Dave is doing Gr10 Maths you will have this in StudentGrade

StudentGradeID, StudentID, GradeID, SubjectID
1, 2, 1, 1

now let's add rows to say John is doing Gr11 Maths and Gr10 English (the new rows have ID 2 and 3)

StudentGradeID, StudentID, GradeID, SubjectID
1, 2, 1, 1
2, 1, 2, 1
3, 1, 1, 2

When you have your data set up like that it's easy to write queries that let you know what grades\subjects students are doing using a JOINs. So If you're talking about Gr10 Maths it'll be a filter on the StudentGrade table where GradeID = 1 and SubjectID = 1, and if the Student table is joined on StudentGrade via a relationship on StudentID you can get the StudentName.

Once you have your tables sorted like above you can do some further reading on joining tables and building Access forms where dropdown fields are the results of selects on the current form and it will hopefully become clearer what to do, but the important thing is that the above data structure will make it possible to write the queries you want and to store the data in a manner you want. Your original database design makes it much harder as there is a lot of "matching" between tables based on text alone and that will always end in tears as you'll write "Maths" for one person and "Math" for another and your data integrity starts to fall apart.
Shakshoka1357 27-Oct-20 12:57pm
Dear, it was awesome explanation specially for a non-IT person like me,
actually the student can have multiple subjects, but withis the same Grade, as the student cant be Grade 10 and 11 same time,
so in my master student table, i make it contains, SubjectID, SubjectName, Student Grade
and insteade of StudentGrade table, i created the StudentSubject table, containing StudentID, SubjectID

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900