Database Design, Forms, Query, etc Help (MS Access)

KVW

I'm making an Access database for my brother. He's a teacher and got stuck in charge of academic awards nights. Small school, about 300-400 kids, grades 7-12. Not much computer wise, so most teachers have to write up all their marks, add them up, average them, give them to the homeroom teacher of each kid, etc. Big hassle. I decided to take a shot at making it easy on him with a simple database. It's been a while since I did any database stuff though and I'm having some difficulties.

First, here's my relationships to the tables.


Big Relationship Pic

Table Descriptions

Teacher Table - contains the unique number primary key (TID) and personal info and the homeroom ID (ex. 9F if they teach a grade 9 class).

Subject Table - Contains the unique text primary key (SubID), which can be "ENG11" for English 11.

Student Table - contains the unique number primary key (SID) and personal info and the homeroom ID (ex. 9F if they are in 9F)

SubjectTeacher Table - contains the unique number primary key (STID), the SubID, which corresponds to the SubID in Subject Table and indicates the subject being taught, and the TID, which corresponds to the TID in the Teacher Table and indicates which teacher is teaching the subject. Multiple teachers can teach the same subject.

SubjectStudents Table - contains the SID, which corresponds to the SID in the Student Table and indicates the Student taking a certain subject, STID, which corresponds to the SubjectTeacher Table's STID and should link a teacher and subject he teaches toa student taking that subject, and the student's marks in the subject he is taking, labelled Term 1 (T1), 2 (T2), 3 (T3), 4 (T4) along with two exam marks, Exam 1 and 2 and finally, a Final Mark.

This SubjectStudents Table is the one giving me the most problems, as I can't seem to create a form to allow quick entry of a student's marks in a certain subject or to have the form sign a student up for a subject.

Some problems I seem to get are with multiple teachers teachign a subject. The teachers show up in their table and can be set up easily with a form to be teaching a new subject, but when I try to do the same for the students, I seem to be running into problems.

Some queries I've beena ble to run, no problem, are All Students in Homeroom "X", where X is any homeroom I want, All Students Taking Subject X, where Subject X is anything I want (note, I can manually add students to a class and get things to work, but it's going to be impossible to do it manually with the various id numbers linking tables) and even Final Marks in various Orders, like by homeroom or Student Name.

My ultimate goal for this database is to have all students and teachers listed in it and the subjects they teach and take. From there, I want to be able to have each teacher login and enter their marks for their class. Once all marks are entered, when the time for academic awards come around, I want to be able to aggregate all the marks for each student and display all students within certain ranges for bronze, silver and gold awards. I know how to set up range and aggregate criteria queries, so this isn't a problem.

However, I just can't seem to work with the StudentSubject Table I've created. It does not have a primary key. Is that the problem? I was thinking it was more a linking table and it just draws from the Student and SubjectTeacher table, so it doesn't really need one. I dont see how it could use one, but I'm open to suggestions. Also, did I make a mistake in the way I set this database up or is it just something simple I'm missing on how to get the data to display in my forms and queries?

Any help is appreciated.

VThornheart

(EDIT: scratch that, on second look your schema said pretty much exactly the same thing as my long winded reply post did... I'll look at it when I get home instead of trying to do it in between looking busy at work )

Indeed, SubjectStudents' Primary Key should be a combination of both the SID and the STID. In the design view, highlight both and then right click and select "primary key". The primary key for that table will become the combination of both, and you'll be able to use it as desired.

SubjectStudents is, for all intents and purposes, what they call a "cross reference" table: it's a table that unites two others in a many to many relationship. The easiest way to express that is with the dual-column key mentioned above.

mspencer

I'm trying to imagine database operations here. First you make a list of all students, all teachers, all subjects, and you populate SubjectTeacher with info on who teaches which class. You have a set of STID's for each class. Each student will be enrolled in multiple STID's. (Consider adding building, room, hour, class length, etc. to SubjectTeacher.)

How do you enroll a student in a class? Create a form not bound to any rows, with fields to select a student and select a SubjectTeacher entry. When the user clicks add, run a macro that validates the contents of the student and SubjectTeacher values, then confirms the student isn't already in that class, then adds the row to SubjectStudents.

How do you find and edit a student's grades? Create a query that lists every single SubjectStudents record, joined with all the other tables needed to provide the info you need. Let the user filter the list of class enrollments by picking things from a dropdown list, and when they finally decide on a specific enrollment, launch a new form that shows only that enrollment's details, editable.

All that probably makes no sense. I'm not sure you can make MS Access do what you want (using that schema) without some creative forms and some macros with program flow control.