Employee Training 11
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 11
In this Microsoft Access tutorial, I will show you how to track employee training and certifications by creating queries to identify expiring and expired courses. You'll also learn how to refresh data views and manage training statuses effectively. This is part 11.
Members
In the extended cut, I will show you how to prevent users from adding the same course twice, lock courses upon completion to avoid accidental changes, and automate the transition from expiring to expired courses. We'll use a record set loop for automatic updates, with optional email notifications.
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Tracking Employee Training Microsoft Access, Certifications Expiring, Employee Course Status, Expiration Date Handling, Expiring Training Notifications, Status ID Usage, Access Requery Function, Training Expiration Automation, Expiration Date Calculation, Employee Role Management, Training Management System
Intro In this video, we'll continue building our Microsoft Access employee training database by modifying queries and reports to identify employees whose training is expiring soon or has already expired. I'll show you how to add the expiration date field, set up aggregate queries for training status, adjust forms and command buttons, and use Access features like refresh and requery to keep your data up to date. We'll also talk about handling expired records, implementing status IDs in queries, and ideas for possible future development such as course prerequisites and bulk updates. This is part 11.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor Richard Rost. Today we make history, folks, part 11. This is the longest TechHelp series I've done. I had to make a part 11. I got graphic. What do you call it? Anyways, if you haven't watched parts one through ten, you're missing out on some good stuff. So go watch those and then come on back.
Alright, so in the last couple lessons, we made a report to see who's missing training. Who is not enrolled in classes that they should be enrolled in based on their role? Who's overdue? In other words, they signed up for a class but haven't finished yet. Their deadline date has passed, and they're still marked as in progress.
Now we need to see who is expiring soon or has expired. This will be very similar to our overdue. So let's just modify this guy overdue. I'm going to copy and paste that. See, you've got a copy, click paste. It's a weird bug that recently has gotten into Access. I've noticed this a couple of times. If you do copy, wait a second and then just hit paste. For some reason, the focus moves, and it doesn't work right. So you got to copy it, click on it again, and then hit paste, weird.
Okay, so we're going to make this employee course status overdue. Let's change this to expiring. I'm going to say expiring because I want to see everybody who's going to be expiring or has already expired. We'll put them up in the same query. And yes, if you want to make it two separate queries, go ahead. It's basically the same thing.
Here's the expiring. Let's go into here, design view. Now what we have to have in here is the status ID has to be completed, which is two. We don't care about the deadline date. What we care about is the expiration date, which I don't think is in this query yet. We've got to add it to this query. Alright, save this. We'll come right back to it. We have to go to the status queue.
And we have to add the expiration date in here as well. Again, it shouldn't cause any problems with the existing query because we're just adding a field from a table. It's already in here. And if you check the number of records, and I did before, it should work just fine.
Alright, so now we got that in there. Let's go back to expiring. A lot of Access development, by the way, is jumping back and forth between different places. I do it a lot. Oh, got to go back to this query. Oh, got to add to the table. Oh, got to go over here. So I do it in my videos because you will find yourself doing it while you're developing. I know that I do.
Okay, so it's going to be two, which is completed. Alright, we don't care about the deadline date. What we want is the expiration date. Now, how far in the future do you want to look? I'm going to say a month. If their expiration date is less than today's date, we'll just go today's day plus 30 days. If you want to get exact with it, you can. If you want to say date, add, you know, today month one, that thing. I'm just going to say anybody who is expiring 30 days from now or earlier because that'll also show expired people.
Alright, save that. And you might want to see here. I don't have anybody here. Let's mark some expired people's. Let's go to myself. Let's say that I've got a couple of things completed here. HR 102. This doesn't have an expiration date. Let's put in here it expired 1/11. So that's a little while ago. And let's say Jimmy Kirk. Let's say he completed SA 101. Let's mark that completed, and the expiration date was 11/5. We'll do one more. This completed, this expires, say 9/1 expired a while ago. Okay. I know the rest of the dates don't make sense, but that's not what we're looking for.
Okay, so now if I open this up, there we go. There's our three records that are completed, but their expiration dates are 30 days in the future or less. And now it's the same thing. Let's add one more for me. Just so we can get me on there, so our count is correct. And let's make this a one. And if I run it now, there are four records. Three people will do the same thing with an aggregate query. So we'll do create query design. Bring in this guy. Bring an employee ID. Turn this thing off. Make it an aggregate query.
We're going to copy this name here. Right-click copy. That's my little trick. And then we go save paste. Say if you go into rename over here, you can copy that. I mean, I see you just right-clicking copy the name. The copy is the whole query. Okay. If I run this now, there's the three employees. Beautiful.
And now on here design, I'm not layout design view, I'm going to slide these down just a little bit more. Copy this, copy paste. Let's change this to expired. Or expiring soon. We're going to training. It's all training. Training is kind of understood. Right. And we'll do overdue. We'll do.
Alright, that's better. Alright, open this guy up. And in here now, instead of overdue, we'll change this to expiring. Make sure that works. There it is. Expired or expiring soon. Okay. And want that button. Expiring or expiring. Click build event. You command open query.
Let's see. Is it in my history? Yep. There it is. I got that windows thing that I mentioned in the last class. And I don't for I think I didn't give you a link to it last time. It's the windows clipboard history. You can turn that on. So windows 11. I think it's in 10. I'm not sure. Anyways, save it. Close it. Close it. Open it. We got our values here. Hit the button. There's your people. And you can make a report. You can do all kinds of stuff. You can make another one that shows expired.
Now this is only going to show people who are marked completed with an expiring date. What about people who are expired? What about people who have like, let's say William Riker is in accounting? We query this list. Let's say he's taken AC 101, but it's expired. Riker is not going to show up in that list. Right. Expiring. Okay. No. Alright. How do we fix that? Well, if you look at the query.
Alright, we've got all the stuff in here. We got status 2 with that date. Now status 4 is expired. So you can just come down here and put a 4 there. Remember, it's "and" across, "or" down. Okay. So if I run this now, there's Riker. He's got one that's expired. Now there's a question. Do you want to see the expired people forever? If someone's got expired training on there, do you want to leave it on there forever? Do you want to put a date on there? You might want to say, alright. If it's been a year, don't show it anymore. That's up to you.
I'm going to have this show all the people who have expired. If they're expired, you should do something about it. Remove them from that role or fire them or whatever. But that's how you do that. Remember, it's "and" across, "or" down. And if you're not familiar with that rule, I cover that in my Access Beginner Series. I also have this tip video on it as well. So go watch this too. It's a very, very handy technique.
But now when you run this, you got a total of four people. And that makes it. And if you want. Now here, we're still seeing three. If you hit F5, that will refresh that. You could put a refresh button on here if you want to. Just copy one of these buttons again, copy paste. And you can just put in here refresh or requery or whatever you want to say. Most users don't know what requery. So I just, even though it is every query, it's a refresh. But refresh works fine. Refresh will refresh these calculations.
Requery actually reloads the data in it. But this form doesn't really have any data as far as records go. So in here, all you really need is a me. Refresh. And that will recalculate. There's also a me. Re-calc. There's a lot of me. Stuff. Alright. So save that.
And back to the main menu. You can see there refresh. Okay. Let's say that Riker fixed his problem. So we come back in here. And this is no longer expired. Let's see. It's completed now. And we close it. We close it. We close it. And we refresh that four should go to a three. And we're good.
Alright. So that's about going to do it. Folks, we covered everything I wanted to cover. If we look at the scheme, we covered it. We didn't cover course prerequisites. I don't think that's super important. But I'll put that on the list for possible future development. Because you can see the roles and you can see what people already have when you add their courses. So we had to figure out exactly how we want to implement this. But it would be that hard to do.
Now I am going to cover some more stuff in another extended cut for the members. We got another bug fix or two to cover. We're also going to make it so you can prevent the user from having the same role twice. So you can't add like HR 101 twice. We're going to lock the course if it's completed. So you don't accidentally change it to something else. Like is right now this is unlocked. You can come in here and just change it to something else. And we'll still say completed. And we're going to automate switching from expiring to expired. In other words, when the database opens up, we're going to have a routine that runs that says, hey, these courses that said expiring soon, we're past that date now. We're going to mark them expired. Generate a list.
We'll use a record set loop to do that. And you could also, if you wanted to in that loop, send an email to someone. I'm not going to do it. I've got other videos that cover that, but I'm just saying there's lots of things you can do once you get to that point. Once again, silver members and up get access to all of my extended cut videos, all of them, not just this one. Gold members can download these completed databases from the TechHelp videos. Everybody gets free classes every month. So that's just another reason why you should become a member.
Now one more thing. I have gotten some emails from people over the course of this 11 part series for things they want to see. So if you are interested in seeing something else added to this series, to this database, let me know either post a comment down below or you can post it on my website in the forum for this, or you can drop me an email if you feel like it.
Some of the suggestions are bulk updates. For example, if you do classroom training and you've got 15 employees that just finished HR 101, you can then just pick the employees and then go, and it'll mark them all as completed instead of having to open up one at a time. That's one option. Another person asked about having a separate front end for employees. They've got an access database right now in their network. And they want this version of the database for the HR manager, but they want the employees to be able to log on and say, hey, I completed this, and then they submit a request, and it gets done.
Someone else said, hey, we could put this on the web. Yeah, we could upload the database to SQL server. Definitely possible. Someone else wanted to see scheduling like classes. Now this might be a separate database completely, but it's definitely a possibility. If you're interested in that, let me know. Of course, enforcing the prerequisites, a possibility. What other ideas do you have? If you've got some, want me to consider them for a possible either a future seminar or maybe a part 12, probably a part seminar, and I'll tell you why.
Obviously, the reason I make these videos is because I love helping people, of course, that's my passion, my love, but of course, I also run a business and I've noticed that when I do these multi-part series, part one gets a ton of views, part two gets a little bit fewer views, part three fewer and so on, all the way down to part 11. Part 11 by the time you get to part 11 we're getting like 10 percent of the people that watch part one so I don't like drawing on these multi-part series too long I find that after a part like part three or four, a lot of people start falling off.
It's the same thing with my regular classes, right, the beginner classes get tons of people and then you know the expert series is 32 classes that people start to fall off through that a lot of the time just because people learn enough to do what they want to do when they stop there. Right there's far fewer people to take developer level 44 than took beginner level three so it's the same thing to keep making these I need people to watch them and if I'm not getting enough eyeballs I got to switch to something else right so that's why I'm going to get to this point, usually part 11 is about the max, is the last, you know, the highest one I've done so far. And then we'll switch to a different topic and then you know so if you are interested in more of this let me know, say something in the comments, send me an email, post something on the website and yeah if enough people are interested I'll make more, but that's going to do it. That's going to do it for your TechHelp video for today. That's going to close this series for now. Part 11 will be the last one for now. Of course members, we got an extended cut coming so don't go anywhere. Hope you learn something. Live long and prosper, my friends, I'll see you next time.
TOPICS:
- Creating a report to identify missing training
- Identifying employees overdue for training
- Modifying queries for expiring training
- Adding expiration date to queries
- Filtering records based on expiration date
- Aggregate queries for training status
- Design tweaks in Microsoft Access forms
- Using command buttons for expiring queries
- Query modifications to include expired training
- Refreshing and recalculating queries
- Implementing status ID in queries
- Handling expired training records
- Concepts of "and" vs "or" in queries
- Using refresh and requery functions in Access
- Future development ideas: course prerequisites
- Preventing duplicate course entries
- Automating status updates from expiring to expired
- Using record set loops for automation
- Potential for email notifications in Access
- Bulk updates in Access training management
COMMERCIAL:
In today's video, we're wrapping up Part 11 of our longest TechHelp series yet. We're getting practical with Access by expanding our database reporting to track expiring employee training. Learn how to modify queries to identify who's expiring soon or has already expired, ensuring you keep on top of staff training deadlines. We'll explore adding and refreshing queries, use the clipboard history for efficient copying, and address expired entries. Plus, I'll discuss features for future updates, like bulk updates, web integration, and learning course prerequisites. If there's more you'd like to see, let me know for possible future content. 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 focus of Part 11 in this TechHelp video series?
A. Creating a new course database
B. Reporting on employees whose training has expired or is expiring soon
C. Designing a user interface
D. Adding a new security feature
Q2. Which status ID indicates that a course is completed?
A. 1
B. 2
C. 3
D. 4
Q3. What must be added to the existing query to track expiring training?
A. The deadline date
B. The course name
C. The expiration date
D. The employee's role
Q4. How many days ahead does the tutorial suggest looking for expiring training?
A. 15 days
B. 30 days
C. 60 days
D. 90 days
Q5. What troubleshooting step is suggested if the copy-paste operation in Access does not work initially?
A. Restart Access
B. Click on the destination again before pasting
C. Use keyboard shortcuts
D. Upgrade the software
Q6. In Access query design, what is the rule for placing conditions that involve "and" and "or"?
A. "Or" across, "and" down
B. "And" across, "or" down
C. "And" above, "or" below
D. Place both in a single row
Q7. What action is recommended for individuals with expired training records?
A. Leave them as they are
B. Remove them from their role or take further action
C. Mark them as pending
D. Ignore the expired status until it's refreshed
Q8. What feature does the Windows clipboard history provide as mentioned in the video?
A. Automatically saves all documents
B. Allows access to multiple copied items for pasting
C. Installs Windows updates automatically
D. Restricts copied text to a single use
Q9. How does the instructor suggest refreshing data in Access forms?
A. Press F5
B. Restart the application
C. Switch the view back and forth
D. Use Ctrl + R
Q10. What might be a reason for the instructor not continuing past Part 11 in this series?
A. Lack of viewer interest beyond the first few parts
B. The completion of all possible topics
C. Technical issues with the video format
D. All relevant information has been covered
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-A; 10-A
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 features a continuation of our longest series yet. This is part 11 of our lessons, where we explore further insights into managing training and course statuses in Microsoft Access. If you haven't seen the earlier parts, I highly recommend going back to watch them to catch up on everything we've done so far.
In recent lessons, we developed a report to track which employees are missing necessary training, who's overdue for completing their courses, and who hasn't yet finished before their deadline. Now, we need to identify individuals whose training will soon expire or has already expired. This task is similar to managing overdue courses, so we'll make adjustments to our current setup.
First, we'll create a new query to display employees with expiring or expired training sessions. After copying the existing 'Overdue' query, we'll modify its focus from overdue to expiring. The relevant parameter here is the expiration date, which we'll add to our query to pinpoint which courses are set to expire within the next month or who has already fallen past expiration.
Upon analyzing our data, we can identify employees and instances that are expired. Should an employee have an expired course, this might necessitate removing them from certain responsibilities or roles. We apply logical operators in our queries to ensure completeness, allowing us to customize the list of employees based on their current status.
Once the query is set, we can save and test it by entering sample data to confirm its effectiveness. We'll also introduce a functionality for querying expired records by including appropriate status codes, ensuring we cover all bases.
Finally, to refresh our data view effortlessly, we could add a refresh button. This feature recalculates values and offers a fresh data read without needing to reload the entire dataset. Any changes, like an employee resolving expired training, will update with a simple refresh.
In conclusion, we've solidified our system's ability to handle training expiry notifications and are set to address further user concerns, such as avoiding role duplications and locking completed courses. All members of our community get access to extended training materials which contain additional features beyond this tutorial.
If you've been following our series and have suggestions or requests for future topics, please let me know through comments or contact me through the website. We're always open to new ideas, be it enhancing this database, implementing bulk updates, or even adapting for employee self-management.
Remember, the full-length video detailing each of these steps is available on my website. Simply follow the link below. Thank you for joining us in this series. Your continued interest makes these tutorials worthwhile. Live long and prosper, my friends.Topic List - Creating a report to identify missing training
- Identifying employees overdue for training
- Modifying queries for expiring training
- Adding expiration date to queries
- Filtering records based on expiration date
- Aggregate queries for training status
- Design tweaks in Microsoft Access forms
- Using command buttons for expiring queries
- Query modifications to include expired training
- Refreshing and recalculating queries
- Implementing status ID in queries
- Handling expired training records
- Concepts of "and" vs "or" in queries
- Using refresh and requery functions in Access
- Future development ideas: course prerequisites
- Preventing duplicate course entries
- Automating status updates from expiring to expired
- Using record set loops for automation
- Potential for email notifications in Access
- Bulk updates in Access training management |
