Recording training attended by staff

[Wendy1]

Hi
I have a database of staff members. There is a table with the details of staff; name, dob, address etc.
I need to set something up that will record the training that staff attend and then be able to print a report of who attended the training session and also a report of training sessions individual staff members attended.
I need to set up fields in a table for date, topic and presenter and then I need to somehow mark off staff members who attended. Please help.
Wendy1

 

[MarkK]

You need 1) a table for your staff, 2) a table for courses in the abstract, like a course catalog, 3) a table for each real-world class, so this is when a course is actually offered with dates and times and stuff, and finally 4) a table that connects who attended which class, and this is where the grades would go too.

Is that the kind of thing you're looking for?

 

[Wendy1]

Yes that sounds like what I am after, but from what you are saying will I need to split the training into 3 different tables?

 

[MarkK]

If you need to keep track of presenter, then you are keeping track of a real-world session, and that will have a date, maybe a room number, and that should have it's own table, because it has it's own distinct dimensions.

And what was taught at that session? Will that subject be taught again, and will there be other subjects? If so, you have another distinct thing to keep track of, which is the abstract list of subjects that may be taught at past or future sessions. See how that list exists apart from any actual instance of a session?

Is a session a multi-day thing? Then you might need an additional table to keep track of what days.

And performance, or grade, this occurs where a person intersects with a session, and that is another table. One class will have many people, one person will take many classes. This is a classic many-to-many relationship and requires three tables to represent.

tPerson
PersonID (Primary Key)
Name

tCourse (like a course catalog)
CourseID (PK)
Name

tClass (real-world instance of a course)
ClassID (PK)
CourseID (Foreign Key)
PresenterID (FK)
DateTime
RoomNumber

tPersonClass
PersonClassID (PK)
PersonID (FK)
ClassID (FK)
Grade

That's an outline of how I expect you'd need to solve this problem. When a new class is scheduled you add a record to tClass. For each person that attends you add a record to tPersonClass.

 

[Wendy1]

I don't need to keep track of the presenter and there are many topics that are usually presented annually on a particular day. Also there is no performance outcome just that the staff member has attended.

 

[jdraw]

I agree with lagbolt's outline. You might consider a field called AttendedYN in tPersonClass to show actual attendance. But a value in Grade would indicate attendance. As I see it tPersonClass identifies a scheduled Class, and it's possible that someone scheduled for a Class may not attend for a number of reasons (sick, vacation, busy with work....).
Good luck with your project.