Employee Training
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access In this Microsoft Access tutorial, I will show you how to track employee training and certifications by building a comprehensive database from scratch. We'll cover table design, relationships, and a bit of VBA scripting to manage course assignments, expiration alerts, and role prerequisites, all while navigating a real-world development scenario. In part 1 we will begin by setting up our database schema (tables, fields, relationships, etc.) PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, Microsoft Access employee training tracking, Access certifications management, track training in Access, certification renewal notifications Access, employee role tracking Access, Access course management, Microsoft Access VBA tutorial, Access database design, Access table design, database schema
Intro
In this video, we'll start building a Microsoft Access database from scratch designed to track employee training and certifications. I'll show you how to plan and create tables for departments, roles, courses, course prerequisites, employees, and the relationships between them. We'll discuss how to manage course requirements by role, record employee training progress, and set up course expirations for certification renewals. While this is a developer-level tutorial and some VBA will be involved later on, most of the work is done without programming at first. This is part 1.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. One of the things I get asked a lot is about tracking employee training and certifications. So I've decided to put together a little video series on doing just that.
We're going to start from scratch and we're going to build a database so you can track employee training. What courses they're supposed to take, what courses they have taken and when, and how long those training certifications are good for. So if they have to renew every year, you'll get a notification.
We're going to start from scratch. We're going to go over our table design and then build it from there. Now this will be a developer-level video, meaning there's going to be some VBA involved. I'm going to try to stick to building as much of the database as I can at first without VBA programming.
All right, so we'll get the table layout. We'll get the tables built. We'll get the forms built. But some of this stuff will require some VBA and I'll try to add that toward the end. But I might sneak in some little embellishments here and there. So if you've never done any VBA programming before, go watch this video. It's my intro to VBA. It'll teach you everything you need to know to get started in about 20 minutes.
Now unlike a lot of my other videos, I have not prebuilt this database. Because I run through ahead of time and I build everything up front, I haven't done that. We're going to be building it together. All I've done so far is put together the table layout that I think I'm going to need, so there may be some other videos that I point you to when we come across certain topics or functions that I think you might need to use. And I'll address that when we get to it.
I figure a lot of the time, you guys learn the best way to build a database. Sometimes it involves seeing me start from scratch and build a database because I guarantee you I'm going to make a couple of mistakes or things that I want to change after the fact. And that happens when you're developing a database. Or sometimes you build it a certain way and you get feedback from your client and they're like, well, no, we don't like it like this. We want it to do this instead. So that might happen too. I don't know. We'll be doing this together. So strap in.
I don't know up front how many parts this is going to be. Probably at least three or four parts. So we're going to do this together. Are you ready? All right. So those of you who've taken any of my longer courses or seminars or even some of the previous TechHelp series that I built, you know, the first thing that I always do is I sit down and I put together my list of tables and the fields that those tables should have. I think this is crucial in understanding what your database is going to do, especially if it's a little more complex like this one.
So let's go over all of these tables and fields and what each one of these things is going to hold. All right. Up first is the department. Every organization has different departments. If you're of a certain size, of course, if you're big enough size where you got to track employee training, usually you have multiple departments: HR, sales, accounting, technical support, and so on. These departments are important for knowing what departments employees are in and also what departments the courses fall under. For example, HR would have their own list of these are the HR courses. We'll talk about that in a minute.
Now inside each department, you've got various roles, and these could be simply just the different levels of that department like you've got HR level one, HR level two, right, sales level one. Those HR level one employees, for example, might be able to do job interviews or exit interviews, but HR level two actually has the power to hire and fire based on the recommendations of HR level one. And depending on the size of your organization, your IT guys, IT level one might be the guy that you call when it's like, oh, my computer won't start. He plugs in the power strip because you kicked it out of the wall. I've had that happen when I used to do tech support. IT level two is the guy that, you know, okay, now we really got to troubleshoot and so on. Or you could set up individual roles between your different departments. Whatever you want to do. For me, that's good enough for me. So that's what I'm going to do.
And of course, between department and role, you can see we have a one-to-many relationship here, right? Each department could have multiple roles assigned to it. I'm not going to draw the little lines. You get it. I made the colors the same. That's good enough.
All right. Next up is the important one, the course table. Now for me, I'm just going to call it courseD. These could also be certifications. If you're not actually giving training, but you have to track their certifications, maybe they have to go to some outside organization to get certified, that's fine too. You can use this for both. You can also put into this table something like a requirement for hiring, like a bachelor's degree in whatever. That's completely up to you. Again, that's just going to be called a course. It's kind of like what I do on my website. I call everything a course and it's listed on your my courses page, even if it's a seminar or a template or a PDF handbook that you purchased. They're all included in the courses list. So this could be courses, certifications, outside education, all that stuff. It falls under the category of courses. Stuff like that is really just a training issue for your users.
Now, courses fall into different departments. We'll talk about how they relate to roles in just a minute. And these will be things like this: HR 101, Introduction to Company Policies, HR 102, Workplace Etiquette and Expectations, and so on. Basic IT, Level 101, logging into your account. Password management. There are all things that people in these departments have to know. And some things all users might have to know. We'll get to that in just a minute. Course ID, obviously, is the primary key. Again, we have a one-to-many relationship. Courses fall under departments. We've got a course code, IT 101. If any of you have gone to college or even high school sometimes, they do this. The course name, description.
Now resource URL is something that I'm going to just leave open. You can put a link to, I'm assuming, on your company website or your intranet or whatever you've got set up, a link to wherever the course material is or more information about it that the user can view, whatever. Expiration number of years. Most certifications that I've come across are only valid for a certain number of years: one year, three years, five years, whatever. You got to recertify. So we'll make a list of who's got the requirement to recertify coming up. That'll be part of this database. Notes, of course, you'll find notes in most tables. And then of course is active because sometimes courses become defunct. This course is the only, you know, HR 102 has been replaced by HR 122, but you don't want to delete it because you still want to know which of your employees have taken this course. We don't delete stuff. I've got a whole video called Don't Delete Data. You just mark it not active. I guess you could do the same thing with every table, but courses are something that usually come in and out more often than departments and roles would.
Okay. Next up, we have the junction table that will relate roles to courses. What role are you in, your HR level one, HR level two, IT level one? Okay. And what courses are required for you to have that position? For example, HR level one, they got to have HR 101, HR 102, HR 103, and IT 101. Maybe everybody's got to have that IT 101. Everybody in the building. Know how to log onto your computer. Well, maybe unless you're in the warehouse, you don't have a computer. I don't know. This is up to you to decide. So IT level one has to have basic HR 101, the IT classes. IT level two might have to have all this stuff plus IT 201, 301, 401. This is where you decide who needs what courses for that job. That's the role requirement table. And I put a sort order on there because you might want to force them to have to take them in a particular order. You got to have IT 101 before you get into IT 102. It's kind of like what I tell my students. You got to take access to beginner one, beginner two, beginner three, all the beginner classes before you get into the expert series and so on. Do you have to? No, but you should.
Here's another table I threw in called course prerequisites. You might have courses that rely on other courses, kind of like mine do. Before you take HR 102, you have to take HR 101 and so on. Generally, it's a one-to-one relationship. Each course has a single other prerequisite, but you never know. You might have something like Leadership 101, which is like your executive track. In order to take that, you have to have had a bunch of other courses before you can even take this one. That's why I made this a many-to-many. You got to have HR, CS, customer service, sales, IT. You got to understand the business before you be putting a leadership role. Again, it's up to you.
Next up, we have our employee table. The employee table is going to relate to some of the other things in a minute. We'll talk about that. But employees are exactly what it sounds like. Employee ID. Supervisor ID is just a link back to another employee. I'm going to make this just one field. Yes, I know in some organizations, you got multiple supervisors. Especially, if you work at Initech. Not only do you get eight different bosses and endless TPS reports, but it's just, it's great. Don't ever steal the stapler. But I think for most companies, most well-run companies, you got one person to answer to. Name, address, all those basic fields. I'm not going to get into it. I got millions of other videos on multiple addresses and phone numbers and all that stuff. We're just going to keep it simple for this one. Hire date and terminate date. Yes, there could be other things in there, but those are the two basics, your job title. Because even though you're HR 102, you might have a funky job title, so we'll just make that text. Notes. And then again, of course, is active because even after you terminate someone, you don't want to delete their record. So that's basic employee information.
Next up, we have a junction table between employee and role because one employee might have multiple roles. They might be in HR and in sales. So you've got the cross reference ID, that's your primary key, employee ID links to the employee table, their role ID links to what role they have. And by extension, you can find out what department that is. You've got a start date and an end date. Notes, the qualified date, what date did they finish their training, what date did they become qualified for that role? And then I added is primary because sometimes people have a primary role. Like, you know, you are the HR manager, but especially in smaller companies, you might jump in and get on the phones and help with sales. So you might be trained in multiple roles. So that's the junction table for that.
And likewise, we now have an employee ex course ID junction table for what courses this employee has taken. We've got an employee ID, the course ID, then you've got the enrollment date, the deadline date, like how long do they have to take this course? You might have like one month from the time you're hired to finish this course or whatever. That's again, completely up to you. This is mostly for reporting purposes. This is so you can generate a report saying, okay, which employees have not finished their training by their deadline dates, that kind of thing. Completed date, expiration date, when does this course expire? And that'll be a calculated field because you got the completed date and then you've got the number of years that it's good for. And yeah, I could have made this number of months or a number of days, but very seldom do I come across any kind of training or certification that's not in years. Or at least if it's six months, you could put in .5, make it a double. And then a status ID, that's just another little helper table telling you where this person is in the progress of taking this course. In progress, they finished it, they failed it. That's up to you.
All right. So there you go. There's our basic database schema. There might be some modifications along the way, but this is where you want to start. You want to go from, you know, a little Excel spreadsheet, that's how I build my stuff. You could do it on paper. You could do it in index cards. I talk about this in a lot more detail in my Access Beginner One class. There will be some tweaks along the way. There always are. But this gives us a place to start with. We have a roadmap. We know where we're going with this database.
So now in part two, we'll start actually putting these tables together. So that's going to do it for part one. That's your TechHelp video for today. Hope you learned something. And if not, I hope it was a good little review on how to build your tables and stuff. Get your tables all set up. Your database schema. Good keyword there. Schema. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Tracking employee training
Tracking certifications
Table design for database
Building a database from scratch
Department table setup
Role table setup
Relationships between tables
Course table setup
Certification tracking
Setting course expiration
Using VBA in database
Role requirement table
Course prerequisites table
Employee table setup
Employee role relationship
Tracking employee courses
Database schema design
COMMERCIAL:
In today's video, we're starting from scratch to build a database for tracking employee training and certifications. You'll learn how to set up tables to track courses taken, certifications, and renewal notifications to ensure ongoing compliance. We'll begin by designing the database schema with tables for departments, roles, courses, employees, and more. While this is a developer-level tutorial with some VBA involved, we'll aim to build most of it without any programming at first. Join me as we embark on this journey together—mistakes and all—and prepare for a collaborative learning experience. This is Part 1, and we'll continue building in upcoming sessions. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary purpose of the video series created by Richard Rost?
A. To teach VBA programming from scratch
B. To create a database for tracking employee salaries
C. To build a database for tracking employee training and certifications
D. To demonstrate complex SQL queries
Q2. What does Richard plan to cover initially in building the database without involving VBA?
A. Building forms and creating SQL queries
B. Table layout, table creation, and form building
C. Advanced reporting and data analysis
D. Building dashboards and charts
Q3. Why does Richard emphasize the importance of a well-thought-out table design?
A. To ensure quick database performance
B. To simplify the backup and recovery process
C. To clarify the database's purpose and manage complexity
D. To make the database visually appealing
Q4. What is the function of the department table?
A. To track employee work hours
B. To categorize employees by their job titles
C. To identify different departments and their associated courses
D. To record employee salaries and bonuses
Q5. What is a key feature of the junction table between roles and courses?
A. It tracks the salary of different roles
B. It records the completion date of courses
C. It determines the required courses for each role
D. It manages employee vacation days
Q6. How is the 'resource URL' field in the course table intended to be used?
A. To link to the employee's personal webpage
B. To provide a link to course material or information
C. To list certifications obtained by employees
D. To reference a database backup location
Q7. What is the significance of the 'is active' field in most tables?
A. To automate course enrollment
B. To mark records that are currently relevant or obsolete
C. To highlight the most important records
D. To track employees' active working hours
Q8. What does the course prerequisites table represent?
A. A list of employees waiting to enroll in courses
B. Courses that are recommended by employees
C. A relationship showing prerequisite courses needed before enrolling
D. A list of courses taken by the department head
Q9. Why might an employee have multiple roles in the database?
A. To record changes in their salary over time
B. To show their participation in different departments/projects
C. To allow tracking of different work shifts
D. To make vacation tracking easier
Q10. What is the purpose of the employee ex course junction table?
A. To record when the employee was hired and terminated
B. To track which employees have failed courses
C. To log which courses employees have taken and their completion status
D. To manage employee payroll information
Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on tracking employee training and certifications using Microsoft Access. I'm your instructor, Richard Rost, and in response to frequent questions about this topic, I've decided to guide you through creating a comprehensive database system from scratch. This will allow you to effectively monitor which courses employees should take, those they have completed and when, as well as manage the renewal of certifications.
We'll start by designing the necessary tables and then proceed with building the database step by step. This series is at the developer level and will include some VBA programming, although I'll initially focus on constructing the database without it. Forms and tables will be laid out first, and any VBA aspects will be introduced later in the series. For those unfamiliar with VBA, I recommend watching my introductory video, which provides a foundational understanding in about 20 minutes.
Unlike previous tutorials, I'm building this database in real-time alongside you. I have prepared a basic table layout, but we'll be exploring and constructing the database together, learning from any necessary adjustments that arise during development. This hands-on approach mirrors real-world scenarios where feedback and revisions are common.
This comprehensive tutorial series will likely span several parts. As always, I begin my process by outlining the tables and fields required. This is crucial, especially for intricate databases. We'll first look at the department table, necessary for tracking which departments employees and courses belong to. Each department could have multiple roles, leading to a one-to-many relationship.
Our next focus is the course table, which I'll refer to as courseD. This tracks both training courses and certifications, potentially including academic requirements. Categories of courses reflect different departments, such as HR and IT, and may consist of specifics like course codes, descriptions, and resource URLs for further information. The table also logs expiration intervals, important for certification renewals, and whether a course is currently active.
A junction table will link roles to courses, specifying what each position requires. This determines the necessary training for various roles. Additionally, we'll consider course prerequisites where necessary, creating a many-to-many relationship to reflect real-world requirements.
The employee table will connect with other components. Key fields include employee ID, supervisor ID, personal information, and employment dates. A junction table will relate employees to roles, allowing for multiple roles per employee if needed. Details like start and end dates for roles, qualification dates, and whether it's a primary role are tracked here.
Finally, we'll use a junction table to document courses taken by each employee. This will include enrollment dates, deadlines, completion dates, expiration dates, and their status, useful for generating progress reports.
This tutorial covers the initial schema of our database, which we'll refine as needed. Establishing a solid starting framework is crucial, and we'll continue constructing our database in the next session.
For an in-depth tutorial and step-by-step instructions on everything discussed, visit my website. Live long and prosper, my friends.
Topic List
Tracking employee training
Tracking certifications
Table design for database
Building a database from scratch
Department table setup
Role table setup
Relationships between tables
Course table setup
Certification tracking
Setting course expiration
Using VBA in database
Role requirement table
Course prerequisites table
Employee table setup
Employee role relationship
Tracking employee courses
Database schema design
|
