Junction Tables - Required Training by Position

[ChrisLayfield]

So moving this from the forms section.

I have a table (tbl_CourseList) of training course (CourseID, CourseName, ExpirationPeriod) and I have a table (tbl_NCS_Role) of Job Roles (NCS_Role, TDLR_Code). To clarify the TDLR is a code which could be made of multiple NCS_Role records (e.g. roles of Field Worker and Data Collctor are part of the TDLR_Code Field Worker/Data Collector/Call Center Interviewer); these will be used for a report.

Course_ID and NCS_Role are the PK for the respective tables. I created a junction table (Jtbl_NCSRole_CourseID) which contains ReqTrainingID (autonumber PK), NCS_Role, CourseID, ReqTraining (Yes/No). I created the relationship of Jtbl_NCSRole_CourseID to NCS_Role and Jtbl_NCSRole_CourseID to CourseID.

The table of NCS Roles is populated already, the table of Courses is not, but I have a form to populate this. This form has all of the NCS Roles listed with checkboxes. Is this how I should populate the junction table, when I add new courses?

What suggestions are there for when a new role is created? Do I make a subform with a query to show the course list and select those which are required?

I hope I posed those questions correctly. The words sound correct, but if you say yes, I have no idea how to go about doing any of that lol.

 

[jdraw]

Did you normalize your table structures as Bob suggested in other post?
Do you have a data model -- independent of your existing records and Access code--?

There are some samples at this site, but none exactly dealing with your situation (at least I don't see one).
http://www.databaseanswers.org/index.htm

Go to the data models, select one and pay specific attention to the "business rules".
You'll be surprised at how much you will learn(about your application) as you layout the things of interest and how they interrelate. Create a list of the things you are dealing with and the details you want/need to store about each. Then, create a list of the questions you want your application to "answer".
eg Does person X in position Y have necessary training?
How many people do not have all prerequisite training?
What training is needed for position Z?

Whatever you feel are the appropriate questions.
If your list of questions can not be satisfied by the data model, then adjust the model as necessary. It is an iterative process.

Good luck.

Bottom line of this -- get your structures correct before you get too involved in the Access/coding.

You may want to post your structures/ questions(rules) to help us understand your situation and to provide advice/suggestions to you.

 

[ChrisLayfield]

Thank you for that link, I have been reading and it is already helpful. I am working on my Db design now. I have started to identify the data to which I will capture and am working on the questions I need to have answered from that data. I am definitely buying a book this weekend as well.

I do have another question about junction tables. When I am making a junction table is it best to have an autonumber assigned for the PK and then the data coming into it or our of it only does so using a FK?

For example: Jtbl_ReqTraining - ReqTrainID (PK autonumber), NCSRole (FK from tbl_NCSRole), CourseID (FK from tbl_TrainingCourses)

 

[jdraw]

Usually the junction table would have as primary key, a compound key made up of the keys from the two tables which caused the creation of the junction table. The junction table would/could/should contain any other fields that are relevant to the combined key of the junction table.
For instance, consider Students( a list of students with ids, name etc.) and Courses (a list of Courses with ids, title, etc. There could be a junction table
StudentCourse consisting of StudentID and CourseId, and StudentCourseGrade and possibly SemesterId or SemesterDate.

No need for an autonumber PK on the junction table.

 

[ChrisLayfield]

ok, so in an attempt to work all this stuff through...I created three tables.
tbl_Job: JobID (PK), Job Title
tbl_Courses: CourseID (PK), CourseName, Expiration
jtbl_ReqTraining: JobID (PK), CourseID (PK), Required (Y/N)

So I complete the table with all courses by id and jobs by id and indicate which are required? Is it better to automate this using a form to select the course by combobox, then use a list box to include the jobs? then somehow if the combination is selected and unique, add it to the table?