School Gradebook relationships

[larrywang]

Hello Expert, I am building a simple database entry where teachers could entry student grades based on their subjects. Here's the problem I run into I am at cross-roads of how to go about it: tblStudent contains basic info plus grades,semesters etc and student's corresponding subject tables for that SEMESTER: tblEnglish, tblScience, tblManagement, tblAccounting etc. I've structured tblEnglish to have following fields ID, StuID, tutorials, journal, report, speaking



Questions: How do I relate them all the subject table to student table? I've tried everything from building relationships to query, nothing works.<br><br>What I want to achieve is, teachers are able to see ALL the student Names like excel datasheet WHO have taken their class and able to put in their corresponding grades in that field (tutorials, journal report, speaking).

Please help, how do I achieve this.

[ranman256]

This sounds like your design is wrong.
You wont have tblEnglish
you have tblClasses , with a code/word to define the type of class, math, history, etc.

I see this structure:
tStudents
---------------
StudentID (autonum)
FirstN
LastN
Addr
...


tTeachers
---------------
TeachID (autonum)
FirstN
LastN
...


tRooms
----------
[room#]
[building]

tCourses
-------------
[courseID]
[Subject] (Science, French)
[level]


tClasses
---------------
[ClassID] (autonum)
[courseID] (long)
[semester] (2015Fall ,2014Spr
[class] (Science 101, Algebra 201
[Subject] (not really needed )
[room#]
[ClassTime] (1pm


tClassRoster
---------------
RostID (autonum)
ClassID (long)
StudentID (long)
FinalGrade


tWork
----------
WorkID (auto)
RostID (long)
WorkType (work,test,quiz,lab
Grade


tAttend
----------
AttendID (auto)
RostID (long)
Date
present (y/n)

[orange]

Here is a free generic model re Student Rosters that may help with your tables and relationships.
This model is generic and may not match your requirements. It is intended as a starting point, and you can add or subtract parts as necessary to meet your requirements.
Good luck.

[larrywang]

@orange,@ranman256 thank you for your quick feedback. Actually I looked into @orange database schema links and went over briefly and thoroughly enjoyed it. You see my database is JUST grade entry for teachers and is no where near what @ranman256 laid out. tblstudents and tblEnglish, tblScience, tblManagement, tblAccounting are all independent tables. How I joined them is via query design, join type-2 in access- in other words students who do not have tblEnglish grades are also in the lists. After that I made a split form and attached to qryStuEnglish and present to teachers all students for THAT Semester 1 (filter sem1 and sem 2 via criteria) and their associated fields (tutorials, journals, report, speaking test). That's how simple I have come up with. Correct me if I am wrong.

Cause I've tried working it out as @ranman256 and came up with complicated lengthy relationships, way over my head. Personally I think query two or more tables is best for my purpose and put them in forms.

[larrywang]

@ranman256 boss, the database schema looks solid. However could you share me your access tables relationship screenshot or even a simple access file where you relate all this tables together. Here I am confused with tClasses and code/word. How do I relate that code/word to another individual table say Algebra 201 where teachers could put that grades to corresponding students. Personally I understand better when it's laid out in a picture.

Thank you boss

[ranman256]

I have no db, it's how I'd lay it out.
yes, there's some flexibility in classes....
i wanted a list of subjects as classes, math,science,history
Then the school year 2015, has a set of these...tClasses ,in semester 2015Fall
2015Fall ,Math101 at 1pm is a classroom.

this classroom has students...tRoster. For that date.

A student in this class can have many...
attendance. TAttend
tests,homework....tWork

[larrywang]

@ranman256 forgive me for being obtuse. boss can u at least give me aforementioned database model schema (in drawing), so that I could implement on my access as learning. Much appreciated.

[ranman256]

I made a change, since I forgot a table...attached are results.

classroom db.zip