Need DB to check if users meet lists of requirements. How??

[hesca]

Hello experts.



DataBase rookie here. I am programming a students database and need to create the necessary tables and queries to specify which courses are required to get specific certifications and to crosscheck this data with the students' approved courses. Tricky deal though cuz most of the certification paths have courses in common.

So, among other tables, I have two that pertain to this issue. These are the names and fields on the tables:

StudentsAndCourses: A list of studentID, CourseID and Grade.
CertificationStages: includes a list of all the certifications along with the carreer each of them belong to AND the requirements(which I havent defined because I don´t know which datatypes and fields would be better for this)

My best guess is to use a lookup field called "RequiredCourses" with multiple values so I can just use the checkboxes to define the requiered courses.

However, I don´t how to make access compare this data against the approved courses on the "students and courses" table. A query maybe? I don´t know.

I tried but the data gets all fumbled up.

So, my question is: what fields would you include in the "certification requierements" table in order to have the database compare the students' approved courses against the "certification requirements" table?

[ranman256]

What if you have a list of the Certs. then pick the ones you need required...(like in photo)
Then you join this picked list to the students_certs to get the results.

[rpeare]

if you're looking for a hint at structure this is what I'd do:

tblStudents (PK autonumber is fine, with student related information)
tblClasses (PK autonumber is fine, with class related information
tblCertifications (PK autonumber is fine, with certification related information)
tblCertClass (PK autonumber is fine, FK to tblCertifications, FK to Classes, this tells you which classes are required for which certifications)
tblStudentClass (PK autonumber is fine, FK tblStudents, FK Classes, this tells you which classes a student has taken)

then when you are checking whether or not a particular person has taken the required classes for a particular certification you'd be checking the information on tblCertClass vs the information on tblStudentClass

[hesca]

rpeare, you´re my hero of the day. Lemme see if I understood.

In tblCertClass I would a field called "cerfication" with many occurrences of the same certification and another field called class, to link each class to the certification. Is that what you mean?

Thanks a mill!

[rpeare]

Both tblCertClass and StudentClass are called Junction Tables. They are relating two tables that otherwise would not relate to one another. So yes, your table tbCertClass would contain your the FK (foreign key) to your both your CERTIFICATION table and your CLASS table. Do not store anything but primary keys in your junction table unless absolutely necessary.