Since you posted to this group, you probably understand that setting up the
right relationships between tables will be important.
You will need tables something like this:
Teacher table (one record for each teacher)
TeacherID primary key
Surname Text
...
Student table (one record per student)
StudentID primary key
Surname text
...
Unit (one record for each subject, e.g. Math 101).
UnitID primary key
...
Class (one record for each time a unit is taught)
ClassID primary key
UnitID relates to Unit.UnitID
StartDate date this unit starts
CoordinatorID relates to the TeacherID from the Teacher table.
Enrol (one record for each time a student is enrolled in a class)
StudentID relates to Student.StudentID
ClassID relates to Class.ClassID
Presumably there is one teacher who teachers (or at least coordinates) the
class, and it is only this one person who has to write the report for the
student at the end of the semester. If so, you could add a Memo field to the
Enrol table where the coordinator can actually write the report.
Note that a Unit is offered many times over the years, so a student does not
enrol in a unit, but in a particular instance of a unit, such as Mrs
Huddlestone's English 102 for Semester 1 2006. This "instance of a unit" is
what is meant by the "Class" table above.
You can then create a report that is based on a query that gets information
from all the tables, and filter it to a particular student and/or a
particular class.
