duplicates and other problems

[Guest]

In my main table I have a lsit of 70 students each with a unique ID. I also
have a field for "advanced competency sign offs" which is a look up table of
more than 60 competencies. When I try to apply more than 1 competency to a
student Access wont let this happen as it says that it is a duplication. Is
it possible to get past this, otherwise I have to have a separate field for
each competency signed off?
Thanks

 

[Nikos Yannacopoulos]

...when I try to apply more than 1 competency to a student

This hints a one-to-many relationship (one student to many competencies)
to begin with, but there is actually more to it...

...otherwise I have to have a separate field for each competency

No, no, no and no! This is absolutely wrong!


Actually, you are dealing with a many-to-many relationship here; a
student may (hopefully) possess several competencies, while each
competence may be (hopefully) possessed by several students. In a
relational database design, many-to-many relationships are implemented
by means of an intermediate table (commonly called a junction table,
among other names) which splits it into two one-to-many relationships,
by introducing an artificial "entity", being a unique combination of one
member of each of the two "sides" of the original many-to-many; in your
case, a StudentCompetence. So, in your example you need:

A students table, like:

tblStudents
StID (Primary Key)
FIrstName
LastName
DOB
(etc)


A competencies table, like:

tblCompetences
CmpID (Primary Key)
Competence
(etc)

And a stbStudentCompetencies (junction) table, like:

tblStudentCompetences
StID (Foreign Key)
CmpID (Foreign Key)

Where the two foreign key fields together are also defined as the
junction table's (composite) Primary Key, so it guarantees uniqueness
(among other things).

HTH,
Nikos