Employee Training 9
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 9 In this Microsoft Access tutorial, we'll learn how to troubleshoot and resolve a bug found while tracking employee training requirements. I'll guide you through identifying missing course data, fixing query joins, and creating reports to showcase missing training. This is part 9 of the series. MembersThere is no extended cut, but here is the database download: 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, database role management, employee course status, missing training report, report design in Access, Access forms and queries, Access error handling, Access report sorting, group levels in Access, employee role course link, bug fixing in Access, query debugging, outer joins.
Intro In this video, we continue the Microsoft Access employee training series with part 9 by troubleshooting a bug where HR course requirements were missing from employee records. We'll walk through cleaning up the list box row source, investigating related queries, and fixing outer join issues to ensure all required courses are displayed. I will also show you how to build a grouped and sorted report that lists employees and the training they are missing, and add a button to the main menu to preview this report for easy access. This is part 9.Transcript Today's part nine of my employee training series, if you haven't watched parts one through eight, go watch those first and come on back. All right, today we're going to start off by fixing a bug that I found. What's that? Wait, you found a bug in your database? Yeah, yeah, it happens. I make mistakes too. And learning how to fix bugs is something that every developer should learn how to do. A lot of the times when you build a database, you have your roadmap all set, you think you've built all the forms and the queries and everything good. You put some sample data in and everything looks fine. But you don't really realize you don't find the bug until you start working with it and putting more data in. This is exactly a bug that I found after I started adding more data to the database. Then I was like, oh, well, there's a mistake. It works well with a little bit of data, but when you start adding more, you'll see that the problem shows up.
Let me describe exactly what the problem is. We've got roles, and each role has courses that are required. HR requires these, Sales requires these, and so on. Accounting requires these. From our employee list, like me, for example, I've got all those courses required. Let's go to Jean Luc. All right, he's got his accounting courses. Now if I add HR to Jean Luc, nothing shows up. Let me make sure. I did this in my sample database. Let me make sure the roles have that in there for HR. Let's see. HR. Yep. Okay. They're in there. So for Jean Luc, they're not showing up. This list should have all those HR courses in it and they're missing. Why is that?
Well, let's start with the data source from this and we're going to work our way back because there are several queries that are required to get this list. I built them all in the previous couple of classes. Let's go backward and deconstruct this and figure out where the problem is.
Starting with this row source for the list box itself. Now, this was made by the wizard. It's a bit of a mess. We can clean this up by simply getting rid of all of that because it's a single query that feeds this list box. We can get rid of that. We don't need those brackets because we don't use spaces in our names. We can make this look a lot cleaner by just doing this. Okay. A whole lot easier to read. So we got that stuff from that query. Let's take a look at this query and see what it's doing. I know these queries have to have this form open. So let's open the form again and let's run that query. That's going to be the employee course missing current queue. All right. That's got nothing in it. So we have to go backward now. Where does this guy get his data from?
All right, so it gets its data from employee course missing queue, this one adds the current to get the employee that's on the current form. Close that. Let's go back to this guy. Let's see what this guy's got. You didn't know if he's missing all his data to design view. This one is employee course status queue and this one just checks the status to see if it's null. If that status ID is null, that means that the employee doesn't have that class. Let's check this thing. So that's this one. Let's open this up. There's a bunch of stuff in here. Let's find Jean-Luc. Okay. There he is. But I'm only seeing the stuff he already has in here, not all of it. Let's take a peek at this guy. So we've got the employee course requirements linked to the actual table that has the courses that each employee signed up for. And that's linked to get the status.
Now, let's take a look at the employee course requirements. The employee course requirements is a list of what they should have. Oh, I'm seeing them all there. That's what each employee should have to have. There they are right there. So why are they not showing up after I join this with the junction table? If you go to design view, this one's got the employee, the role, what their role is, and what their role requires. If we run that, we can see the employee, each of his roles and what is required, what courses are required. That then comes together into this query to match up with what's in his actual table. But I'm not seeing everything in here. Why is this?
Well, this took me a minute to figure it out. But the reason why is because we're only joining this based on the course ID. We also have to make a second join between this table and this table with employee ID. And of course, we have to make this an outer join. We want to join everything from the requirements to the junction table that has what classes they have. I want to make it so that I only see records where the course ID equals this, that course ID and this employee ID also equals that employee ID. If I save this and run it now, look at this. This is what I'm expecting. I'm seeing all of these and notice these cells down here, these fields down here are all null, they're all blank because there's no matching record in this table. It joins them, it pulls everything in from this table, but it's not showing it with a value because there is no value. Now that I've got this one correct, then the one above that is going to be correct. Look at it, see. That's the ones he's missing now.
Now when I run this, wait a minute, I'll end. I have to close and reopen it. So I always close and reopen stuff. There we go. See? Now it works. I'm seeing the stuff in there that was missing before because of a bad relationship, a bad link in our query. It caused that problem. Likewise, if I check other people, you can see, oh, I'm missing HR 101. Yep, I definitely am. James Kirk, he's got everything for sales. Let's add HR to him. Let's see, yes, there it goes. That's working. Okay. And Deanna, she's missing classes.
I discovered this bug because I was going to make this lesson about, let's make a query that shows everybody who's missing stuff. Who's missing courses? We're going to do who's missing them, who has expired courses, who is going to have expired courses soon. We're going to get to all this. I discovered we already have this guy, which is based on this guy. This query here has everybody in it who's missing courses. I'm missing that. Deanna's missing these. Jean Luc is missing these. You could make this into a report. Let's make a report real quick out of this. We'll group it based on employee ID. I'm going to bring last name into this one too.
In design view, we have to put it in course status. You can very carefully, you can add fields like that to the queries underneath it without worrying about messing things up. But I definitely want last name. Employee course status queue. Let's see employee course status queue design. Employee course requirements. We have to put them there. Sometimes when you're building these, you don't think ahead of time and think down the road where you want to have in all of these. We definitely want last name in there. Save that. Close it. Now if I open this one up, we have first name, last name employee ID. What course is there missing? We can now make a report based on this. Let's go find my blank R copy and paste. We'll call this the employee missing course R. I try to keep everything singular if I think about it. This is going to be based on the employee course missing queue.
In design view, set the data source, the record source, that guy. Now we can add existing fields. What do we want? Let's bring in everything. I keep this label around just for formatting. What we're going to do is add a grouping level. We'll group it on employee ID. In report design, go to group and sort down here. We'll add a group based on employee ID. I think a header is fine. We only need a footer for that. Inside that group, underneath that group, we'll sort by last name and then we'll sort by first name. I'm going to turn the grouping off now. In our employee ID header, we're going to put the stuff that is all about the employee.
See, and then it's copy and paste. Right undo. I hate that. Cut. Click paste. Nope. Come on. I swear this is a bug that just got introduced in Access recently. I've noticed this over the past couple of months. Whenever I select multiple objects, if I try to cut and paste... Okay. Now it works that time. Sometimes I have to do them one at a time. Sometimes not. If you have that problem, let me know. I'm going to move these labels up here into the page header. Actually, we don't need these. Let's give it this will be self-evident. We'll do last name, first name, you could put those together if you want to employee ID. This will be the employee header. I'm going to move this label up into the report header and this will just be simply. Use with missing training. You can make it look pretty. I'm just making a functional. Don't really need that page header. This we don't need. The role we don't really need. We'll do the course name. Actually, I can get rid of all these labels.
We'll do the course name. Actually, put the course code. Course code. Course name. We really don't need this stuff either. Slide these over here. Truncate that up a little bit like that. That should be good. Save it. Close it. Right click preview. And there we go. There's your report. Yeah, I got some prettifying to do, but you see that it works. First thing I would do is turn off the borders around everything. Select all this stuff. Format. Shape outline. Transparent. We can add a first name here. And do one of these things. Right. We'll do LF. This will be equals last name. And comma space and first name. Right. You can put the employee ID all the way over to the right if you want to. Save it.
Let's take another peek preview. That looks a lot better. I don't like the alternating rows here, but that shading actually kind of makes sense if we do it like this. Let's take this. Let's turn off that alternate background color like that. And let's just make the regular background color light gray with that. Like so. Make this transparent. Fill transparent. And what we'll do is make this guy have no alternate back. Okay, good. Now it looks good. Click the preview. Oh, yeah, there we go. I make this one transparent too. That looks nice. I spend lots of time in my full course going over lots of different formatting options and stuff. I'll give you some links in a minute. Let's fix that one. It's going to bother me. Okay. And then this of course has to be bigger. Sorry, I just have to.
Okay, save it. Close it. Close it. Take a peek. Preview. There we go. Close it. Missing training. And I got a list of who is missing what training in each class they're missing. Maybe sort these two. Design view. I always find that it's easier to design reports when they're maximized. Turn those grouping level back on. Where we got see we got what ID first name last name. Let's add a group and sort by course code after that. We don't really need a header for it though, but you can just make that zero width. Actually, we don't want a group on that. We want a sort on that. That's my bad. And a sort first go. There we go. Save it. Close it. Preview it. Okay, that's better. There we go. Now, let's make a way to get to this.
Let's put a button on the main menu. We're going to expand our menu a little bit. This stuff can come over here. This will be our employee. Our employee database. Okay. We'll come up. And we're going to do a reports section. So copy paste will slide you over here. Missing training report. Right click build event. Bring up our code builder. And we're in command 17 click because I forgot the name of the button. All right, sorry, Alex. What are we going to call it? Missing training button. I'll go back in here. Okay, here we go. I'm in the detail click now. Okay. I'm leaving that in the video too because I do it a lot and a lot of you might do it. If you accidentally missed the button right click here and go to build event. Now you're in the detail click event. This is what happens if you click on the detail section of the form. We don't want that. Do we? No, we do not. Right click on the button build event. There we go. Missing training button. I had some blank spaces in here. We can get rid of those.
Do command dot open report. What's the report name? I forgot already. Employee missing course R. How do you want to open it? We're going to open it in preview. AC view preview. You open it in normal mode. It's going to go right to your printer. That's it. We don't need anything else really because it's just one report. There's no special criteria. We need to write anything. It's just going to show you everybody. There it is. Looks good. Now one thing I would like to do is I would like to count the number of people that might be missing training and put that right underneath there maybe in a little box. So you can see before you have to run the support, you can just see it on the main menu. Right. They got four people missing training. We'll start doing that. In tomorrow's video. And a bunch of other stuff. We got lots of stuff.
Before you go, if you want to learn more about error handling and debugging, check out this fast tip video. I also spent a lot of time on debugging and error handling in Access Developer 2 and in Developer 15. I do the beginner basic stuff in 2. And then in 15, we go over in detail. I call it my debugging level two video, which covers lots of stuff. And if you want to learn more about those sorting and grouping levels in Microsoft Access reports, watch this video. That's also covered in detail in my Access Expert Levels Level 12 class sorting and grouping levels.Lots of reporting stuff is covered in this video. Somebody asked me what videos cover reporting. I don't really have a video that is all about reporting. Although I've been thinking about putting something like that together because I get asked it all the time. What video covers reporting? What video covers forms?
I do a little bit of this, then a little bit of that. A little bit of forms, a little bit of tables, a little bit of queries. I don't like the depth-first approach where you learn everything there is to know about tables and then everything there is to know about queries. I do a little bit of this, a little bit of that. I have more of a breadth-first approach to training. That's how I like to do it.
Alright, so that's about it for today. What's today? Today is Monday. Monday, the 18th of November, 2024. So tune in tomorrow, Tuesday, same bad time, same bad channel. Unless of course you're a member and you can watch it right now because that's one of the benefits of being a member.
I'm going to probably record a couple more lessons tonight and I'll set you up for the next couple of days. But that's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 10.
A special thank you and shout out to our sponsors. First, we have 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:
Fixing a bug related to missing HR courses
Deconstructing the list box row source
Cleaning up the query from the list box
Investigating the "employee course missing" query
Understanding joins in the query
Creating outer joins based on course and employee ID
Building a report for missing employee courses
Adding fields to queries for report purposes
Designing a report with grouping and sorting
Formatting the report layout and appearance
Sorting courses by course code in the report
Adding a button to the main menu for navigation
Implementing a button to open a report in preview mode
Counting missing training instances on the main menu
COMMERCIAL:
In today's video, we're continuing with part nine of our employee training series. We're tackling a pesky bug in our Access database - don't worry, I make mistakes too! It seems when we add HR requirements to an employee's courses, they don't all show up. We'll fix this by cleaning up our row sources and untangling the query mess. I'll guide you through setting up a report to display employees missing course training, and we'll even create a shortcut on our main menu to access it. Lots of fun debugging tips today! 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 was the main topic addressed in today's video tutorial?
A. Creating a database from scratch
B. Debugging a bug in an employee training database
C. Designing a new user interface for the application
D. Improving database security measures
Q2. What was the specific issue identified with Jean Luc's courses?
A. The courses he completed were not saved
B. HR courses were not showing up in his list of required courses
C. Duplicate entries were appearing for each course
D. His accounting courses were incorrectly listed as HR courses
Q3. What was the reason for the missing HR courses in the list for Jean Luc?
A. The database server was down
B. An incorrect join condition in the query
C. The courses were not entered into the system
D. The software version was outdated
Q4. Which type of join was necessary to fix the bug in the query?
A. Inner join on course ID
B. Cartesian join on all fields
C. Left outer join between the requirements and the junction table
D. Right outer join on employee ID
Q5. What was the ultimate goal after fixing the query bug?
A. To delete expired courses
B. To generate a report listing employees who are missing courses
C. To restructure the entire database
D. To update course requirements
Q6. In the process of creating a report for missing courses, what additional field was specifically added?
A. Course duration
B. Employee title
C. Employee's last name
D. Email address
Q7. How did the presenter suggest improving the report's appearance?
A. Adding images to the report header
B. Making borders around elements transparent
C. Changing the font to a script style
D. Including detailed course descriptions
Q8. What does the presenter plan to add on the main menu in the next video?
A. A button to view completed training
B. A list of all available courses
C. A count of people missing training
D. A link to external training resources
Q9. Which additional related video content does the presenter recommend for further learning?
A. Server maintenance tips
B. Office productivity hacks
C. Error handling and debugging techniques
D. A comprehensive guide to VBA programming
Q10. What was the presenter's approach to teaching new material in this series?
A. Depth-first, focusing on one topic until mastery is achieved
B. Breadth-first, covering a variety of subjects iteratively
C. Randomized, choosing topics at random
D. Sequential, following a strict order based on database components
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-C; 10-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 covers part nine of our employee training series. If you haven't watched parts one through eight, I suggest doing so before continuing with this session. Today, we'll address a bug found in our database. Yes, even I encounter bugs, and learning how to fix them is a crucial skill for any developer. You might feel that you've completed your database's layout and tested it with some data only to discover issues as more data comes in. This is exactly what happened here.
The issue revolves around roles and the courses each requires. Each department like HR, Sales, and Accounting has specific course requirements for employees. While reviewing an example with a fictional employee, Jean Luc, adding HR to his profile showed no courses listed, despite them being present in the database for this role.
To tackle this problem, we'll trace back through the data sources and queries that culminate in this list, examining each to determine where the error lies. This involves inspecting the list box's row source, initially created using a wizard. Clean-up includes removing unnecessary complexities and simplifying the query since there's only one feeding the list box.
Next, we examine the form and run the "employee course missing current queue" query, which presents no results. This requires further investigation into its source, leading us backward through other queries, like the "employee course missing queue" and "employee course status queue." These queries check course enrollment status under certain conditions, such as null values indicating an employee hasn't taken a necessary class.
By examining these queries, we find the "employee course requirements," linking employees to required courses, highlights the problem. On realizing the missing connection, it's discovered that missing join clauses in the query caused it to neglect associations between course and employee IDs. Properly establishing these relationships and adjusting the join type resolves the issue, allowing for comprehensive data retrieval.
The solution involved ensuring correct outer joins and relationship matches. After verifying, we see the expected results—Jean Luc's missing courses are displayed properly again. Reassessing other employees confirms this correction works across similar cases.
With the queries fixed, I explain how to create a report that lists employees missing necessary training. By grouping entries by employee ID and organizing the data with additional sorting measures, such as last name, we develop a clear, functional report. Adjustments are made for formatting clarity—from adjusting headers to eliminating unnecessary details. The report is reviewed efficiently, detailing missing courses per employee.
Further adjustments are done on the main menu interface by adding a button that launches this new report. After setting its click event properly, this tool becomes easily accessible. While doing this, I noticed a persistent bug sometimes affecting multiple object selection, a recurring minor annoyance I've encountered.
Lastly, I share plans for future tutorials and debugging techniques, emphasizing my breadth-first approach to teaching that touches on a variety of topics. You'll find excellent resources on error handling in my Access Developer courses and more detailed sorting and grouping instructions in Access Expert classes. For complete video tutorials, including step-by-step guidance on what's been discussed, please visit my website at the link below. Live long and prosper, my friends.Topic List Fixing a bug related to missing HR courses
Deconstructing the list box row source
Cleaning up the query from the list box
Investigating the "employee course missing" query
Understanding joins in the query
Creating outer joins based on course and employee ID
Building a report for missing employee courses
Adding fields to queries for report purposes
Designing a report with grouping and sorting
Formatting the report layout and appearance
Sorting courses by course code in the report
Adding a button to the main menu for navigation
Implementing a button to open a report in preview mode
Counting missing training instances on the main menu |
