This is my first post to this group so I would like to first say hello
and thanks in advance. My name is Jay. I am designing my first
database in Access 2007. To help me out I have been reading the
"Access 2007: The Missing Manual". I have reached my first conceptual
roadblock and I though I would try to get help here. I am not looking
for someone to do my work for me. I am just looking for a nudge in
the right direction.

What I have to work with:
Students(about 3000)
Instructors(about 40)
ClassDescriptions(23 possible)
BasicCurriculumGroup(10 of the ClassDescriptions)
IntermediateCurriculumGroup(14 of the ClassDescriptions)
AdvancedCurriculumGroup(6 of the ClassDescriptions)

What I need:
A way to track attendance and grades per lesson for each student. I
would also like to keep track of which instructor taught each lesson.
I created a table for each of the Students, Instructors, and
ClassDescriptions. I created a table for each of the three curriculum
groups each one has the fields [LessonNumber] and [LessonTitle].
[LessonTitle] is a lookup from the ClassDescriptions table. I think
what I want to have is two more tables, one to track grades per lesson
and one to track attendance. I want to make sure that the way I set
this up prevents a student from jumping from one curriculum to
another, keeps track of when a student has missed a lesson within the
curriculum they are enroled in, and in the future, allows me to print
a report that shows the grades the student got for each lesson.

Data Idiosyncrasies:
Lessons are one on one(student to instructor). The last two advanced
lessons will have one instructor and severial students (maybe as many
as 6). Students will be assigned a curriculum at the time their first
lesson is entered into the system(one of Basic, Intermediate or
Advanced). Data will be supplied from each instructor via an excel
spreadsheet at least once a month per instructor. That file will
contain the following fields
[StudentNumber],[CurriculumType],[LessonNumber],[TimeArrived],[TimeLeft],[EvaluationGrade].

My problem:
I thought I would want relationships like (A student has a curriculium
which has x number of lessons, each of which has an instructor, time
in, time out, and a grade). I am not sure if the 3 tables (one for
each curriculum type) was the right decision because of the way data
arrives from the instructors. And if it isn't the right decision, what
would be a better way to set this up and where did I go wrong in my
thought process? To put it anouther way, how do I build the
relationship between one student and only one of three tables when I
don't know which table the relationship needs to link to until the
time of data entry?

Thank you again,
Jay