Additional table

[tmarsh]

I have a small database that tracks staff training. Main tables are:

tb_staff
tb_courses
tb_courses_done

1 staff can take many courses. I want to introduce booking - staff can book a course but not necessarily attend. Can I just add a table, say tb_course_booked and use an update query to add to tb_courses_done as needed? Would that work?

Thanks.

 

[wazz]

1 staff can take many courses, but 1 course can be attended by many staff. so, you need a composite (junction) table. in this composite table you can include the date of the course, the date people signed up, whether or not people attended and whether or not people finished (etc).

tblStaffCourse
StaffID (FK)
CourseID (FK)
CourseStartDate
SignUpDate
Attended (Y/N)
CourseCompleteDate

if staff can take a course more than once, the PK would be made up of the first three fields; otherwise the first two.

 

[tmarsh]

1 staff can take many courses, but 1 course can be attended by many staff. so, you need a composite (junction) table. in this composite table you can include the date of the course, the date people signed up, whether or not people attended and whether or not people finished (etc).

Thanks. So, is the information stored in this table when I enter it on a form? What becomes of the training done table?

 

[wazz]

naturally. when you create the form you will have to change the StaffID and CourseID controls to combo boxes so you can make your selections. the StaffID combo box's Row Source will be a query of all Staff members and the CourseID combo box's Row Source will be a query of all courses.

you won't need the training done table. is there data in the table? can you move it?

 

[tmarsh]

naturally. when it you create the form you will have to change the StaffID and CourseID controls to combo boxes so you can make your selections. the StaffID combo box's Row Source will be query of all Staff members and the CourseID combo box's Row Source will be a query of all courses.

you won't need the training done table. is there data in the table? can you move it?

Yes it contains all training done. it's linked to the staff table 1:n. My main form has staff details linked to a subform with training done details.

I'm not expert on access, I developed this database thanks to people like yourself on these forums so maybe I'm not thinking along the right lines.

I'm wondering - if this junction table contains all courses (booked and completed) what way do I work the not completed ones? Essentially all I want to keep is courses done. I want bookings so I can say how many staff are booked at a certain time and how many actually complete a course. After that I don't want courses not attended showing with completed courses, if you see what I mean?

 

[wazz]

if staff have to tell someone they are dropping a course, then the person they tell can delete the booking by hand or tell the appropriate person to make the deletion, or, you could automate the process by creating a delete query based on rules of your own devising, such as: if, at the end of a course there are no attendance records or no marks entered into a record, delete that record.

I want bookings so I can say how many staff are booked at a certain time and how many actually complete a course.

if you want to see how many actually complete a course, then you don't want to delete the bookings. you can still create a report that tells you only which courses were completed.

you might also consider keeping the bookings to see which courses are regularly dropped, or not attended, etc. what do you think? i can make a quick sample db if you're not sure about the junction table.

 

[tmarsh]

you might also consider keeping the bookings to see which courses are regularly dropped, or not attended, etc. what do you think? i can make a quick sample db if you're not sure about the junction table.

Yes I think I would keep the bookings, presumably I could filter the form so that only completed courses were visible?

A sample database would be good thanks. I've been working on a junction table but I'm not sure exactly what I'm doing!!

 

[wazz]

i've included a new table called tblCourseDate. without it, you would have to enter the dates of the course every time for everyone who signed up. this way, whenever you offer a course you put the dates here one time.

it's a bit rushed but i think i included all of the basic layout. you could change the DateCompleted field to a check box, for example; and you could filter for only completed courses.

 

[tmarsh]

i've included a new table called tblCourseDate. without it, you would have to enter the dates of the course every time for everyone who signed up. this way, whenever you offer a course you put the dates here one time.

it's a bit rushed but i think i included all of the basic layout. you could change the DateCompleted field to a check box, for example; and you could filter for only completed courses.

Thanks very much for that, that's excellent. It's easier to see how it works with an example. I'm going to try and adjust the database I have but if not I'll just do a new one.

 

[neileg]

I know Wazz is being helpful, but this is a regular topic and there's plenty in the forums already on class bookings.

 

[tmarsh]

I know Wazz is being helpful, but this is a regular topic and there's plenty in the forums already on class bookings.

I suppose I made the mistake of searching for many to many, here's what I got:

Sorry - no matches. Please try some different terms. The following words are either very common, too long, or too short and were not included in your search : many, to, many


I always search first and on several forums. This is my favourite though!