2 Xtra fields causing brainache

[Pharcyde]

In my studentToAssessment table i have the fields StudentID-AssessmentID-ResultID-ResultDate-DatePaid-AssessorID

and it's relationship wit the assessors table can be seen in the attached screenshot...

All i need to do is enable each assessment to have up to 3 Assessors. instead of the 1 it currently has.

Can anyone spread a li'l knowledge my way?

Cheers, Lee

________________________________________________

P.S I have already tried simply adding AssessorID2, and replicating the relationships but it creates a new table in elationships and still doesnt work...
_________________________________________________

 

[The_Doc_Man]

The moment you have more than one of something that applies to something else, you have grounds for splitting the tables. So if a student can have more than one assessor, you have to split the assessor from the student.

I will also make the leap that one assessor can deal with more than one student in the same time period. That is ANOTHER reason to split the student from the assessor.

My friend, you have a many-to-many relationship here. The only way to do this reasonably with Access, any version, is to have a linking table between them.

tblStudent
- ST ID
- other student information

tblAssessor
- AS ID
- other assessor information

tblStAsLink
- ST ID (fk)
- AS ID (fk)
- any limiting information, such as perhaps a test ID for which this linkage applies.