Problem with Primary Key

[Pharcyde]

Problem with PK

I have a problem with repeating data in my tables.

3 tables: Students, Assessments, and StudentsToAssessment.

with the following keys: Students: StudID, Assessments: AssessID, StudentToAssessments: StudID&AssessID

This is correct for normalisation, but causes problems when putting in assessment results:

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
i.e
StudID - AssessID - Result
123 - Exam - Fail
123 - Exam - Pass


Wont work because it causes a duplicate value, fair enough. I can see that

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

Problem being I'm not sure how to get past this because a student can pass/fail an exam. When failed he can resits however many times....
How do I sort this? Cheers, Lee

 

[FoFa]

Student ID is normally a unique key to that student's information. Not sure what assesments is but if it is Pass/Fail a checkbox would work, but I am going to guess it is more than that, and a lookup table basically. Student to Assesment is not needed, but you do need an exam taken table. This would/could contain multi-entries for a student (retook the test), contain the student ID, could contain the assesment or a pointer to the lookup table, a date the test was taken, etc. Could even contain information on the exam taken (like MS Access 101).