How do I fit in extra tables?

[Jane]

I have 3 tables as follows:

tblCourse (courseID (Primary), name, start date, fees, ...)
tblStudent(studentID (primary), DOB, mother's maiden name,
nationality, ...)
tblStudentCourse(courseID (Primary), studentID (primary),
selection outcome, fees paid, assessment result, ...)

The first two are linked with the last in the middle.
There is a 1-many between tblCourse and tblStudentCourse,
since a course has many students.
Also, there is a 1-many between tblStudent and tblCourses,
as a student may atend many courses.

My problem is that I want a subset of tblStudentCourse, as
quite a few fields relate to 1 course only and another
bunch of fields relate to 1 other course only. But all
the rest of the fields relate to all the courses.

I have created 2 tables with the extra stuff
tblCertExtraStuff(courseID (Primary), studentID (primary),
exam result, project, assessment, ...)
tblDiplomaExtraStuff(courseID (Primary), studentID
(primary), FeesYear2, Fees Year3, ...)

How do I relate the 2 extra fields to the other three?
Any help would be greatly appreciated.

Thanks

Jane

 

[Steve Schapel]

Jane,

You can actually do this on the basis of the fields you already have.
But for convenience/simplicity, I would add an Autonumber field to the
tblStudentCourse table, and make it the primary key field of that
table, and then have a number data type field in the tblCertExtraStuff
and tblDiplomaExtraStuff tables to link to tblStudentCourse.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thanks Steve. I'll do that now.
I was going mad trying to figure it out!

S