Training Matrix Database

[hinchi1]

Hello



I've not been on here for a while but I am looking to create a database for work colleagues to show what training courses they have completed. I'm ok creating the tables and some level of queries (I will probably be coming back to the forum for further support on this topic). I was just looking for you thoughts on a few ways of inputting data to a form or forms, specifically the training courses attended:

Tables needed(for now):

tblTechnicians
tblTrainingCourseList
tblTSLevel
tblBuilding
tblRooms

I know how to do cascading comboboxes, which is one approach i was thinking to do, but my thought is that anyone person will have done several courses. So, if I was going to enter a New Record of an individual i would select their name from a drop down box. I would do this for TSLevel, Building and Rooms, but for the training course list i was thinking that clicking a button for the training list, a listbox would display with all available training courses that we do. From the list I can then select the relevant courses for the technician. If, by mistake I select an incorrect course can I just deselect from the list and then press ok to commit the records. It has been a while since I did any database work so I am probably asking daft questions here. With regards to the tblTechnicians there will obviously need to be a field to store completed courses, what format would that need to be to store multiple lines of data. I know there is a form where you can select multiple choices from a left textbox and then move across to the right textbox but I cannot remember what you call that. Thanks for your time and patience.

[mike60smart]

Hi

You need a basic MainForm / Subform Setup

Main Form to select the Person

Subform allows you to enter 1 or more courses to attend

[CJ_London]

3 tables

employees
courses
employee courses done - to contain employeeFK, courseFK, date completed, perhaps more

main form based on employees table
subform based on employee courses done table, using a combo control to select the courses

[hinchi1]

What would be a good way to select additional courses my colleagues may do from a list, without showing the ones they have already done in the list? How would multiple courses be stored in a table against an individual? Thanks for the tips. I just need to refresh my memory on subforms. How do I do a table on courses done?

[mike60smart]

Hi
Are you able to upload a zipped copy of your database without any confidential data?

[hinchi1]

Hi
I've not really started it yet. Just creating the tables and messing around with form layouts. I just wanted some ideas of the best way to approach the task. My last project I felt it could have been better, more streamlined if I had asked for support and advice much sooner in the design process. Sorry to be a little vague.

[CJ_London]

What would be a good way to select additional courses my colleagues may do from a list, without showing the ones they have already done in the list?

modify the combo rowsource to exclude them

[hinchi1]

[QUOTE=CJ_London;516581]modify the combo rowsource to exclude them[/QUOTES

Thanks for this, not sure how to do that to be honest. It's early days so I will look in these and other forums for options. Rather than use combo for selecting courses I may use the listbox option.

[ArviLaanemets]

On Fly! Probably you need at least some of tables below (your further design depends on which ones you'll have)
tblEmployees: EmployeeID, ForeName, LastName, EmployedAt, LeavedAt;
tblAssignments: AssignmentID, Assignment (you need this table and next one, when different assignments need different sets of courses);
tblEmployeeAssignments: EmployeeAssignmentID, EmployeeID, AssignmentID, AssignmentAt;
tblCourses: CourseID, Course;
tblAssignmentCourses: AssignmentCourseID, AssignmentID (you need this table, when different assignments need different sets of courses);
tblEmployeeCourses: EmployeeCourseID, EmployeeID, CourseID, CourseStartAt, CourseEndAt, ...;
...

You'll have a single form to display employee info (e.g. fEmployee), with query from table tblEmployees as source. The query returns info for employees with date field LeavedAt empty;
You'll have a continuous form to display employee's courses (e.g. fEmployeeCourses), with query from tblEmployeeCourses as source;
You create a subform (e.g. sfEmployeeCourses) in fEmployee, with fEmployeeCourses as source, using EmployeeID to link the forms (LinkChieldFields and LinkMasterFields properties in subform);
In form EmployeeCourses, in datarow you'll have a combo box to select the course (e.g. cbbCourse). By default, its rowsource may be simply a query, which returns all courses from tblCourses. You create an Current event for fEmployeeCourses, which updates the RowSource query of cbbCourse in this form, updating the query to limit the returned list with ones not having CourseID present in tblEmployeeCourses for this employee <...FROM tblCourses c WHERE CourseID NOT IN (SELECT CourseID FROM tblEmployeeCourses WHERE EmployeeID = [txtEmployeeID] AND CourseID <> c.Course)>. In case the available courses depend on employees assignment too, you also have add to rowsource query joins to tblEmployeeasignments and to tblAssignmentCourses, and use them to add a WHERE clause to limit the available courses to ones allowed for employees assignment.

[CJ_London]

Thanks for this, not sure how to do that to be honest.

I would adapt the 'cascading combo's' technique which you say you are familiar with - list all courses when the control does not have the focus, list all courses not taken by the employee when the control does have the focus

[hinchi1]

Thanks for this, really useful

[hinchi1]

Thanks for this. Not sure how to do the rowsource qry joins to the tbls but i will have a look, but i may be back to you on this one. Thanks again.

[hinchi1]

On Fly! Probably you need at least some of tables below (your further design depends on which ones you'll have)
tblEmployees: EmployeeID, ForeName, LastName, EmployedAt, LeavedAt;
tblAssignments: AssignmentID, Assignment (you need this table and next one, when different assignments need different sets of courses);
tblEmployeeAssignments: EmployeeAssignmentID, EmployeeID, AssignmentID, AssignmentAt;
tblCourses: CourseID, Course;
tblAssignmentCourses: AssignmentCourseID, AssignmentID (you need this table, when different assignments need different sets of courses);
tblEmployeeCourses: EmployeeCourseID, EmployeeID, CourseID, CourseStartAt, CourseEndAt, ...;
...

You'll have a single form to display employee info (e.g. fEmployee), with query from table tblEmployees as source. The query returns info for employees with date field LeavedAt empty;
You'll have a continuous form to display employee's courses (e.g. fEmployeeCourses), with query from tblEmployeeCourses as source;
You create a subform (e.g. sfEmployeeCourses) in fEmployee, with fEmployeeCourses as source, using EmployeeID to link the forms (LinkChieldFields and LinkMasterFields properties in subform);
In form EmployeeCourses, in datarow you'll have a combo box to select the course (e.g. cbbCourse). By default, its rowsource may be simply a query, which returns all courses from tblCourses. You create an Current event for fEmployeeCourses, which updates the RowSource query of cbbCourse in this form, updating the query to limit the returned list with ones not having CourseID present in tblEmployeeCourses for this employee <...FROM tblCourses c WHERE CourseID NOT IN (SELECT CourseID FROM tblEmployeeCourses WHERE EmployeeID = [txtEmployeeID] AND CourseID <> c.Course)>. In case the available courses depend on employees assignment too, you also have add to rowsource query joins to tblEmployeeasignments and to tblAssignmentCourses, and use them to add a WHERE clause to limit the available courses to ones allowed for employees assignment.

Just read through this again and thanks for the thorough information. The database I am planning is just show training courses that people have attended, such as 1st aid or manual handling. There will be no assignments involved, so would I start with the following tables:
tblEmployees
tblCourses
tblEmployeeCourses

Obviously, I will want to be able to add new employee names and courses, and add courses to existing employees, but I am sure I can figure this bit out.
Thanks again

[davegri]

Sounds basically like a many-to-many table setup.
This link shows how the tables and forms can interact in that situation.
Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

[hinchi1]

Sounds basically like a many-to-many table setup.
This link shows how the tables and forms can interact in that situation.
Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

Hi Davegri
This is a great DB for what i need. Am I ok to change things around on this or is this just for viewing? Thought it best to ask first.