If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DATABASE DESIGN
-- Pam I want to use a subform on my personnel form to track training for department personnel. I need something that can track training eveloutions, instructor and total hours of training achieved by each individual. I already have a subform on my personnel data form but I can't seem to make it do what I need it to do. If anyone has a template for a training data base that I could use as a strawman for developing a more efficient design, I would be grateful. Thanks in advance. |
#2
|
|||
|
|||
DATABASE DESIGN
The place to start is with the database design. In a typical training
database you would have a Course table and an Employee table, plus a third table to serve as a junction table. Each course can be attended by many employees, and each employee can attend many courses. This is a many-to-many relationship, so you need a junction table to resolve the relationship. The tables may be something like this: tblCourse CourseID (primary key, or PK) CourseDescription Instructor CourseDate etc. tblEmployee EmployeeID (PK) FirstName LastName etc. tblEnrollment (junction table) EnrollmentID (PK) CourseID (foreign key, or FK) EmployeeID (FK) In the Relationships window, drag EmployeeID (tblEmployee) and CourseID (tblCourse) onto the like-named fields in tblEnrollment. Click Enforce Referential Integrity each time. Create a form (frmCourse) based on tblCourse, and another (fsubEnrollment) based on tblEnrollment. On fsubEnrollment, create a combo box bound to EmployeeID (that's EmployeeID in tblEnrollment, the subform's Record Source). Use a query based on tblEmployee as the combo box Row Source. When you create the query, make EmployeeID the first column. In the second column you could have something like: LastFirst: [LastName] & ", " & [FirstName] Use the combo box property sheet to set the Column Count to 2, the Column Widths to 0";1.5", and the Bound Column to 1. The Bound Column is the one you will store. Use the toolbox to draw a subform/subreport control on frmCourse. Set this control's Source Object to fsubEnrollment, and set the Link Child and Link Master fields to CourseID. Now you can enter a course description into frmCourse, and by way of the subform populate the course with employees. This approach assumes you already have a table containing the Employee information. There are several factors that could complicate this. If there is a list of courses such as would exist at a school, you need to take that into account. Also, you may need a separate table for instructors. Just a note that each time I say something like "set the Column Count" I am referring to choices on the property sheet. To see a form's Property Sheet, open the form in Design View and click View Properties. For a control, click the control to select it, and use View Properties if the Property Sheet is not already open. Poke around a bit and you'll probably get the hang of how the Property Sheet works. Post back with any specific questions. "Pam" wrote in message ... -- Pam I want to use a subform on my personnel form to track training for department personnel. I need something that can track training eveloutions, instructor and total hours of training achieved by each individual. I already have a subform on my personnel data form but I can't seem to make it do what I need it to do. If anyone has a template for a training data base that I could use as a strawman for developing a more efficient design, I would be grateful. Thanks in advance. |
#3
|
|||
|
|||
DATABASE DESIGN
I would propose a modification to BruceM's design. A course can be given many
times by different instructors on different dates, and this information should be kept separate from the general, unchanging description of the course. Also, an instrcutor table is recommended. So the schema looks like this: Course table: CourseID (PK) CourseDescription Prereqs Instructor table: InstructorID (PK) FirstFname LastName Session table: SessionID (PK) CourseID InstructorID Place CourseDate Enrollment table: EnrollmentID (PK) EmployeeID SessionID Employee table as per BruceM If you want to get fancy, you can add a Course-Instructor junction table that records which instructors are qualified to teach which courses. -- TedMi |
#4
|
|||
|
|||
DATABASE DESIGN
Not every training database relies on a course catalog approach to training
sessions. Where I work most training is a one-time event as prcedures are updated or new procedures and processes are implemented. There is some recurring information such as records of periodic meetings, which are included with training because they have instructional elements, but these are so different from one meeting to another that the only thing they have in common is the general description. When I suggested a design I pointed out that if there is a group of standard courses it would affect the approach. I didn't want to get into a lot of "what if" information without knowing more about the project, especially since I had already written a fair amount, so I appreciate your filling in the gap for the "course catalog" contingency. "tedmi" wrote in message ... I would propose a modification to BruceM's design. A course can be given many times by different instructors on different dates, and this information should be kept separate from the general, unchanging description of the course. Also, an instrcutor table is recommended. So the schema looks like this: Course table: CourseID (PK) CourseDescription Prereqs Instructor table: InstructorID (PK) FirstFname LastName Session table: SessionID (PK) CourseID InstructorID Place CourseDate Enrollment table: EnrollmentID (PK) EmployeeID SessionID Employee table as per BruceM If you want to get fancy, you can add a Course-Instructor junction table that records which instructors are qualified to teach which courses. -- TedMi |
#5
|
|||
|
|||
DATABASE DESIGN
"tedmi" wrote in message ... I would propose a modification to BruceM's design. A course can be given many times by different instructors on different dates, and this information should be kept separate from the general, unchanging description of the course. Also, an instrcutor table is recommended. So the schema looks like this: Course table: CourseID (PK) CourseDescription Prereqs Instructor table: InstructorID (PK) FirstFname LastName Session table: SessionID (PK) CourseID InstructorID Place CourseDate Enrollment table: EnrollmentID (PK) EmployeeID SessionID Employee table as per BruceM If you want to get fancy, you can add a Course-Instructor junction table that records which instructors are qualified to teach which courses. If your courses are taught by people who are also employees, the InstructorID field in the Session table should point to the Employee table, not a separate table. -Amy |
#6
|
|||
|
|||
DATABASE DESIGN
It gets a bit murky when the instructor may be either an employee or
somebody from outside the company, which was my situation. In the end I decided to store the name. Many instructors are one-time only, so creating an instructor table, then somehow using a query or something to combine it with the employee table while maintaining some sort of key field, seemed more complex than was needed. I'm storing data redundantly, but it seemed to be one of those times when breaking a rule was the best choice. "Amy Blankenship" wrote in message ... "tedmi" wrote in message ... I would propose a modification to BruceM's design. A course can be given many times by different instructors on different dates, and this information should be kept separate from the general, unchanging description of the course. Also, an instrcutor table is recommended. So the schema looks like this: Course table: CourseID (PK) CourseDescription Prereqs Instructor table: InstructorID (PK) FirstFname LastName Session table: SessionID (PK) CourseID InstructorID Place CourseDate Enrollment table: EnrollmentID (PK) EmployeeID SessionID Employee table as per BruceM If you want to get fancy, you can add a Course-Instructor junction table that records which instructors are qualified to teach which courses. If your courses are taught by people who are also employees, the InstructorID field in the Session table should point to the Employee table, not a separate table. -Amy |
Thread Tools | |
Display Modes | |
|
|