Employee Training 6
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 6 In this Microsoft Access tutorial, you will learn how to identify gaps in employee training and certifications by developing complex queries. We will focus on determining missing courses for employees based on assigned roles and creating a system to efficiently track and display these requirements. This is part 6. MembersIn the extended cut, we will learn how to automatically add courses to an employee's training list with a single click. I'll show you how to set up the functionality so when you select a missing course, it gets marked as "in progress" in the employee's record. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, Employee Training Tracking, Employee Certifications Database, Missing Courses Queries, Query Design, Role-based Training Needs, Role Requirements, Course Table Linking, Course Completion Status, Inner Joins, Outer Joins, Employee Course Requirements Query, List Box for Missing Courses, Access Query Optimization
Intro In this video, we continue building the employee training tracking database in Microsoft Access. You'll learn how to design and refine queries that match employees to their assigned roles, determine what training is required, and identify which courses each person is missing. I'll show you how to use joins between tables, create queries for missing training, and add a list box to display missing courses for each employee, as well as set up requery functionality to keep the list up to date. This is part 6.Transcript This is part six of the employee training tracking database. If you haven't watched parts one through five, you're not going to get anything out of this one. No, but I'm just kidding. This is where we start to have fun. Previously, we've got our employees set up, and we know what roles this employee belongs to, like HR level one and sales level two. We know what training they've had.
Now, today we're going to figure out what they are missing. This person's assigned to these two roles. Here's what he's completed. What is left for this person to do? What classes does he need to maintain his certification, or what are we going to call it, to keep his job?
All right, so today we're going to start by building a couple of kind of complicated queries. But this is the best way to quickly find out what this person is missing. So follow along with me as we put together some queries.
First, we're going to build a query to determine what a particular employee needs based on the roles that he's been assigned. Once we have a list of what he needs, all of the classes that he needs, then we can kind of reverse that and say, all right, what's he missing?
Let's start with what he needs. So let's create query design. All right, we're going to bring in some tables here. Let's start off with the employee T. All right, there's our employee. Give me employee ID and first name just so we can see who it is. It's just me at this point.
Now we need to know what roles this employee has been assigned. So now we need the employee X role T. Bring that in. And that should make that join right there. Let's show his role in here. For that, we'll need to go to the role T. Right, and that'll link that one up. From there, we can show the role. But I don't want it to be called description in here. So let's alias that. We'll just make that say role like that.
Now if I run it, I get this. All right, here's Richard. That's me. The only one in the system so far. HR level one, sales level two. That's correct. So far, we're cooking with gas. Now based on the roles, I'm going to slide this over. I know it's going to be off the screen for you guys, but this is a pretty big query. I got a really wide monitor. It took up a lot of space on mine too.
Now we need the role requirements. So we know based on the role, what courses are required. OK. Now this one, let's make this even wider. This one will link us to the course table. Bring in the course table. Now I can bring in the course name and see this should show me now. Look at this. This will show me for HR level one, I need all of those. Let's bring in the course code too.
Where's the course code? Is that one make more sense? Yeah, I mean, this is what I don't like working with. It's really wide ones. There's the course code. All right, there we got our course code. That's all the training that this person needs.
At this point, we need to find out which courses he's taken. So where does that come from? Well, let's go to add more tables. There comes a point at which your query gets to be too complicated. So far, this has all been relatively straightforward. All inner joins, nothing crazy going on here. These are all nice, normal joins.
To find out what courses this employee has taken, now I need the employee X course team. This comes in, it finds a couple of different relationships here. They're correct. Course ID and employee ID links back to the employee table. OK. That should be OK. If I run this now, OK, I'm getting data. But I'm only seeing the courses that I've taken. That's to be expected because with that inner join, it basically says only show where the matching records are equal.
To fix that, I would have to change this to an outer join, show me all the records from employee X course T and the other ones where they're equal. If I run it now, I get that. And because outer joins, it's now too complicated. Now it can't figure out what's going on.
This is the point at which you say, OK, stop here. Get rid of that table. Stick with the data that we know that we have. Let's run it. This is good. OK. Now we're going to take this data and make another query and join in that employee X course T. All right. Stop. As soon as you get, do it step at a time, run it each step of the way. Make sure you're getting the right data that you expect. As soon as you do, we're going to save this. Employee course requirement Q. Or just req. What have you got?
All right. We got this. This one's working good. We're good. OK. We got a list of each employee, their roles, and what courses they need. Now we're going to make another query. We're going to take that query that we just built, that guy. Now we're going to join it to the list of courses that the employee needs.
Here, I'm going to bring in all of this. You know what? I messed up. I didn't bring in the course ID. We need the course ID in that one. Hold on. Let's take a step back. See, I goofed too from time to time. Go back and edit this guy. We need the course ID to make that relationship. We can just grab that one right there. Any one of these will work. All right. Save it. Close it. Now we'll do that again. All right. Create query design. Bring in this guy. Bring in this guy. We're going to join them based on course ID. We're going to make that an outer join.
Show all records from here and the other ones where they're required. Someone's beaming in. Hold on. Someone's beaming in. Who is it? Yeah. It's just a pad full of troubles. All right. Bring in all of this stuff over here. This will show us now. If I run this, this will show us all of the courses that this person needs with the course IDs. OK.
Now over here, this is what they've actually taken or are signed up for. Let's take a look at the status ID and run it. Oh, look at that. There's the three classes I've actually taken. There's the other ones that I have not. They're null. I have a separate video if you're not familiar with this concept of how to do this. Go watch my Outer Joins video. That's a very good one. It's like you've got customers in contacts, but you're not seeing all the customers. Why? You have to make an Outer join. Once you do, you'll see all the customers. If they have contacts, it'll match up. Same thing here.
I know before most of my TechHelp videos, when I've run through it myself ahead of time, I give you a list of prerequisites. But with these longer series, and I haven't run through this ahead of time, I'm just going to show you the videos as we come to them. OK.
Now at this point, if you want to see what that status is, OK, let's add the status table in here. Status T and bring down the description. Hang on. OK. All right. And because outer joins again, what happened? Well, if you have a one to, you call it up, an Outer join here, you have to make this one an Outer join as well, because this might have a null, which means this would be a null, which then it can't make a join here. You just have to do this. That ought to fix that problem. OK.
Let's save this one as the Employee Course Status Q. Now, it'd be very easy to make a very similar version of this, OK, that only shows us ones that are missing. All right. Yes, yes. I know you can just put the is null in an SQL statement and stuff. But it's just easier sometimes if you have these multiple queries handy. I'm going to create another version of this, create query design. I'm going to pull in that status Q. All right.
I'm going to bring in all of the fields. I'm going to say where the status ID is null. We don't want two of these. So I'll hide it. Now, if I run it, it's going to be the same thing. Now it's just showing which courses this person is missing. OK, which ones are they missing? So we're going to save this one as Employee Course Missing, Q. And I'm going to want to keep this around because I'm going to want to generate a list of this for all of my employees. So I'm going to leave this one here. All right.
That's all employees. I'm going to make another version of it that's just for the current customer. Just for the one, that is, where are we? I've got to go. What happened? There it is. I scrolled so many times left and right. I lost it. I'm going to make another version of that that just shows the missing courses for the open customer. So again, one more custom query. This is why sometimes your database has 5 million queries in it. Honestly, it's a lot of the times it's easier to build the database this way with making custom queries than what I do is I go back later on. I say, OK, which ones of these queries can I maybe optimize and put directly in the forms and reports? But I always start off with tons of queries.
We're going to take this missing one now. Again, we're going to do the same thing. I'm going to bring in all the fields. But the employee ID is going to be forms employee F, employee ID. This one will be run whenever this form is open to show this person's missing courses. This will be, let's call it Employee Course Missing Current, Q for the current employee. OK.
Let's test it to make sure it's working. If I run it now for the current, let me move this over a little bit. That should show just the ones he's missing. OK. Let's go over to Jimmy Kirk here. Let's add him into, do I have computer service? What are the ones that I put roles in for? Let's see, roles we got HR, we got sales. Let's put Jimmy Kirk just in sales. Employee form, Jim Kirk. Let's put him in sales SA level one.
If I run this now, good. He's missing the sales level one class. So it's working. OK. If you run your other query, it should show everyone's missing courses. This will be important later on for making a report and say, hey, show me all the employees and who's missing what. All right.
Now I'm ready to make a list box showing the courses that this person is missing. I'm going to do it in a list box over here. Doesn't really make sense to put it in a subform. Because we're not going to be able to make changes to it. So this box is perfect for this. Design view. Let me save a little space here because I know we're running out of room. Put it right over here. That's based on this query. OK.
Form design, list box is right there. Drop it here. Look up the values with table of query. We're getting it from the missing current Q. What fields do we want? The course ID is going to be the bound field. That's what's important. Then I want to see the course code and the course name. Everything else is kind of meaningless. All right. Next. What do you want to sort on? Course code is fine. Next.
This is what it's going to look like because this query can't generate any data right now because it requires this form. You're just going to have to kind of guess. Now, so this is based on a query, you're not getting the checkbox there to hide the key column. So we have to manually hide the key column ourselves. The course code is going to be about yay big. The course name is probably about yay big. If you want to see data in here before you make this list box, just go out here and take that parameter out of the query. Or make it, and you can always adjust the column width later if you want to. If that's too narrow. Next. What is the bound field? That's the course ID.
Now, what are we going to do to it? We're not going to save it in a field in the employee table. So we're just going to hang on to that and remember it for later use. At this point right now, we just want to see what this person is missing. OK. Next, what label do you want? I actually am going to use this label. Let's call this missing courses like that courses. Then finish, and the label comes in right there. So we're going to have to go move it up there like that and sit. Sit, Ubu sit. Good. OK. Remember that stuff.
There's my missing courses. Let me try to get that lined up nice and perky. Everybody lined up nice. OK. Ready? Save it. Close it. Open it. Open. There you go. There we go. I'm missing my sales 200 classes and my IT classes.
Now, this thing will need to be requeried when you move from record to record. If I move to Jim Kirk, nothing tells this guy to requery itself. So that's got to be done. So in here, design view, this guy's name is List 36. That's a no-good one. So we're going to call this missing course list. We're going to go into the on current event for the master form, parent form, the on current event. Remember, the on current event runs when you move from record to record, or when you first open the form. Want to learn more about on current? I wrote a song about it. Want to hit it? Here it goes. Remember, is that one?
This is going to simply be missing course list dot requery. That's it. That says when you move from record to record, requery that list. OK. Close it. Close it. Save it. Let's debug compile. Everything looks good. Now we'll put it up.
Let's move to Jimmy Kirk. Oh, look at that. Jimmy Kirk's missing stuff. Over here.
Now, you might want to manually requery this once in a while, too, because if I go to Deanna Troy and I add her as HR level three, OK, that's not going to requery this. You're going to have to put an event in here. Maybe a manual button over here. The manual button is the easiest thing for now. We'll deal with all those little missing events and stuff later on. For now, I'll just throw our manual requery button here. This will be smaller. There we go.
Right click. Actually, let's give it a name first. Requery button. We're going to go right click, build event. We might add more stuff to it later.But for now, we just need this. There. When you click the manual, we'll be in query mode. If you go here, here, all right, HRI level three, she's missing that. Let's say she's got HRI level two and HRI level one in here as well. All right, but you need to requery it for that all to show up. You could also put events in here to trigger all that. We'll get to it eventually.
Now, I know I said in the last video that I was going to hold all of the extended cuts for the very end, but I lied. I was doing this earlier. I was playing around with it. And I'm like, oh, it'd be really cool. If we could click on one of these and it adds it over here and puts in the in-progress, instead of you having to come in here and type in HRI 101. And then in-progress. How about if we could do that automatically? Guess what? We can. Watch this.
I was playing around a little earlier. Watch this. Look at this. Let's go Jim Kirk. Let's go to Deanna. I'll put her in the HRI class as HRI 101. I'll go to her in the roles. All right, requery this. That's what she needs. You're going to add them one at a time. And I'm going to ask, hey, hey, get this double click. Like that. Or hit the add all button. Boom. Add them all with one click. All the stuff she needs. And we're going to do that in the extended cut for the members.
Today, right now, is yes. I just, I did that. I'm like, you know what? I got to do it. I got to do it. So I do have a whole late long list of stuff I am going to have in other extended cuts. But members, we're going to do this right now. So if you're not a member, sign up now. Silver members and up get access to all of my extended cut videos. And gold members can download these databases and you get the code vault and everybody gets some free training. You get some free training. And you get some free training. Everybody gets free courses. So check it out. Click that join button right now and sign up.
But that's going to do it for part six. Don't worry. There's a lot more free stuff coming. We got reports and all kinds of stuff to do still. This isn't the end. OK, I just wanted to throw in an extended cut right now in the middle. So we're going to do a lot more stuff. OK, but that's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part seven.
A special thank you and shout out to Juan Soto with Access Experts Software Solutions, Manufacturing Experts, Specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist. And he not only offers access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your access project, Sammy is your guide. Check them out at ShamaConsultancy.com.
TOPICS:
- Building queries to find missing training
- Query design for employee training needs
- Joining tables to determine employee roles
- Alias role descriptions in queries
- Linking roles to required courses
- Handling outer joins in complex queries
- Creating queries for completed courses
- Saving query states for later steps
- Testing and refining queries for accuracy
- Identifying missing courses for employees
- Utilizing the outer join for missing records
- Creating a list of missing courses for employees
- Manual requerying of missing course list
- Setting up an on current event for course list
- Adding manual requery button functionality
- Extending queries to handle more complex actions
COMMERCIAL:
In today's video, we're continuing with part six of the employee training tracking database. We'll explore how to determine what training your employees are missing to maintain their job roles. You will learn to create queries that identify required courses, assess completions, and highlight gaps. I'll guide you step-by-step as we build queries to discover what's left for each employee, create tailored lists of needed courses, and use a requery button for updates. Get ready to enhance your database with practical techniques. 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 primary function is discussed in this part of the training database tutorial?
A. Tracking employee attendance
B. Determining what training courses an employee is missing
C. Setting up new employee profiles
D. Managing employee payroll
Q2. What is the first step in building a system to determine missing courses for employees?
A. Create a report for missing courses
B. Enter data into the employee table
C. Build a query to determine a particular employee's needs based on assigned roles
D. Set up employee certifications
Q3. Which table contains the roles assigned to an employee?
A. Employee T
B. Employee X course T
C. Employee X role T
D. Course T
Q4. Why does the query need to be adjusted to use an outer join?
A. To list only completed courses
B. To ensure all records are shown, including those with no matching entries in certain tables
C. To speed up the query processing time
D. To eliminate duplicate entries
Q5. What is the purpose of saving a query as "Employee Course Requirement Q"?
A. To find all employees missing courses
B. To maintain a list of courses an employee has completed
C. To track the dates employees last accessed the system
D. To have a list of necessary courses for each employee role
Q6. How is the problem of not showing all necessary courses in the query resolved?
A. By rewriting the SQL code from scratch
B. By creating a separate query for each employee
C. By using inner joins
D. By switching to outer joins and ensuring all relationships are established
Q7. What type of database object is used to display missing courses on the employee form?
A. Subform
B. Combo box
C. List box
D. Text box
Q8. Why is a requery needed when navigating between records on the form?
A. To update the list of courses an employee has completed
B. To manually add missing courses to the record
C. To ensure the correct missing courses are displayed for the selected employee
D. To refresh the entire form design
Q9. What additional feature is proposed for members in the extended cut of the tutorial?
A. Automated payroll calculations
B. Automatically adding missing courses as in-progress when selected
C. Generating customizable charts for training data
D. Setting reminders for upcoming training sessions
Answers: 1-B; 2-C; 3-C; 4-B; 5-D; 6-D; 7-C; 8-C; 9-B
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 is part six of the employee training tracking database series. If you haven't watched the previous parts, you might find it challenging to follow along. But don't worry; we're going to have some fun today. So far, we've established our employee setup, assigned them to roles like HR level one or sales level two, and tracked the training they've completed. Today, we'll determine what training is still required for each employee to maintain their certifications or ensure they meet their job requirements.
To start, we'll construct a couple of queries. These queries will help us identify what training an employee is missing. First, we'll build a query to figure out what specific training is needed based on the roles assigned to an employee. After we compile a list of required classes, we'll reverse it to see which classes are still missing.
We begin by using Query Design and bringing in the necessary tables. First, we need the employee table to get employee ID and first name. Next, we'll determine the roles assigned to the employee. We need to bring in both the employee X role table and the role table to view these roles, ensuring to rename any columns for clarity.
Once we establish the roles, the next step involves linking to the course table to identify which courses are tied to these roles. By bringing in the course table, we'll see the required courses and their codes. Then, our task is to figure out which of these courses the employee has already completed. We do this by linking to the employee X course table, which reveals the courses taken by the employee. Initially, we see only courses that match, requiring a change to an outer join to view all courses, including those still needed.
When our query becomes overly complex, it's wise to break it into smaller steps. We save our progress as Employee Course Requirement Q and continue to build upon it. Now, we'll design another query that matches the required courses against those the employee is actually missing by joining these with outer joins to the course data.
If you're unfamiliar with outer joins, think of them as a way to display all potential records, even if they have no direct match. This is similar to showing all customers with or without contact information. By refining our query to track only missing courses, we create Employee Course Missing Q. This reveals any outstanding training needs per employee.
For ongoing tracking, we design a query just for the current employee, Employee Course Missing Current Q. This query uses an employee ID from the open form to list missing courses specifically for that employee.
To visually display these missing courses, we add a list box into our form. Instead of altering via subforms, the list box effectively presents data without modification options. Adjusting this list box requires hiding and arranging fields correctly for readability. It's important also to set up a function to requery and refresh the form when changing records, ensuring the list box always reflects accurate training requirements.
For convenience, you might need additional controls to manually refresh listings after changes. This can be managed with a simple requery button, facilitating updates and maintaining an up-to-date display of training needs.
While I initially planned to hold extended tutorials until the end, I've decided to showcase a special feature where missing courses can be added directly to an employee's record with a button click. This capability is covered in the extended cut available to members. If any member wants to access this advanced content, it is available to Silver members and above.
That's a wrap for part six of our tutorial. More free content is on the way, including reports and additional functionalities. To watch a full video tutorial with step-by-step guidance on everything we've discussed here, check my website at the link below. Live long and prosper, my friends.Topic List - Building queries to find missing training
- Query design for employee training needs
- Joining tables to determine employee roles
- Alias role descriptions in queries
- Linking roles to required courses
- Handling outer joins in complex queries
- Creating queries for completed courses
- Saving query states for later steps
- Testing and refining queries for accuracy
- Identifying missing courses for employees
- Utilizing the outer join for missing records
- Creating a list of missing courses for employees
- Manual requerying of missing course list
- Setting up an on current event for course list
- Adding manual requery button functionality
- Extending queries to handle more complex actions |
