I have a database that tracks staff training in a health trust. It's pretty simple, tb_staff, tb_courses_done which is 1:n and a tb_courses 1:n on the staff_id of tb_courses_done.
I want to know how to go about adding a table(s) that will allow me to book staff onto courses and track if they attended or not. At present it's 1 staff member has many courses so any courses entered are "done." I have a main form with staff details and it has a subform where I add completed courses.
I think you need to revist the design, as you have already identified
you probably need
a table to contain details of courses (eg course type / name)
a table detailing the schedule of courses (ie when and where)
possibly a table defining where courses are held
possibly a table defining people planning / or booking to attend a specific course (ou could include in this the outcome of their attendance (pass / fail / resit)
you may find other tables defining linked or related courses usefull, possibly a course type eg midwifery, neonatal
these tables would replace the course attneded table you already have.
strictly speaking a staff member may attend many courses, its conceivable one staff member may attend multiple courses of the same type (if say they fail and have to resit the course)
equally a course may comprise multiple sessions
a course may comprise multiple sessions (either different days for the the same material, OR mutliple dates for the same / related material). IE A course may have repeat sessions throughout the year, or may comprise mulitple sessions)
so you have a 1:M for a member of staff on an instance of a training course
you have a 1:M for a course which comprises individual sessions or instances of the course