Employee Training 10
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 10 In this Microsoft Access tutorial, I will show you how to track employee training and certifications by creating an aggregate query to count missing training records and prepare your main menu to display this information at a glance. We will also explore how to identify overdue training sessions. This is part 10. 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, Tracking Employee Training, DCount Function, Aggregate Query, Employee Course Missing, SQL subquery, Status ID, Deadline Date, Query Design, Object Dependencies, Overdue Training, Domain Aggregate Functions, Missing Training Report, Employee Certifications
Intro In this video, we continue with part 10 of the Microsoft Access employee training series. I'll show you how to create an aggregate query and use the DCount function to display a count of employees missing training right on your main menu. We'll step through modifying queries safely, adding fields, filtering for overdue training, and updating the user interface with text boxes and buttons for easy access to important reports. You'll also learn how to use the object dependencies tool to avoid breaking existing queries. This is part 10.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today is part 10. Yes, we're up to 10 already of my employee training video series. I was just thinking that 10 is the longest series I've ever done. I think the association videos that I did went up to 10. So after this, we're going to be breaking records with part 11, but we're going to let's get to part 10 first, right? People, all right. Again, of course, if you haven't watched parts one through nine, go watch those first and then come on back. Here we go. All right, so last time we made this cool report, right? But I want to know just at a glance that there are four people whenever I log into the database, right? I want to know that there are four people that are missing training, and it shows me right here. And then I know I've got to run the report and find out who it is. All right, so we're going to build this first.
All right, so back to the copy that doesn't have it in it, and the reason why I wanted to run through it first is because there is a trick you can do in some other databases where you can make the DCount function have a subquery in it directly. It kind of looks something like this. You could say DCount and then star or whatever the field is, and then in here you could put an SQL statement, right, select distinct. That's basically an aggregate query from this query, but unfortunately in Access, you can't do it this way. So we need to make an aggregate query first. All right, so let's create. If you don't know what an aggregate query is, pause the video now and go watch this guy. This video explains aggregate queries. It's basically taking a query with a bunch of different records in it and grouping it on something like we're going to group all of the employees together. Right, you can also do sums and averages and all kinds of counts and all kinds of stuff. And if you don't know what DCount is, go watch this. DCount, we're going to use to count the number of records in a query. So we're going to count the number of records that are in that aggregate query and display that on the main menu. Got it? Okay.
All right, so first up is the aggregate query. Create query design now. I want to use as my source that query we were working with before, the employee course missing queue that shows all of the missing employees. Now, in here, all I really care about is a count, so I'm just going to bring in, you think you might want to bring in the star. Trust me, bring in the employee ID. It'll run faster, which is that one field. I know, I know it's a minuscule thing, but these little tiny changes, especially if you upscale this later to SQL Server and it's running on a server and there's tens of thousands of records, and just one field is all we need in here. Okay, now we're going to turn this into an aggregate or total query, and that's going to group by that. That's all I got to do, just leave it alone, save it as employee, can't type today, employee course missing agggq. That's my little phrase. I know it's an aggregate query then. Okay, and now if I run it, that's all I get, one, two, three, four. Why? Because it takes a look at its input, which is that, and it groups all the like values together. So you get one, two, three, and four, and it's in no particular sorted order. It just happened to give you that order. Oh, someone's beaming in.
Well, I'm, who is it? Now I'm kidding. No one's beaming in. That's just my hourly, my half-hourly. I changed it to a half-hourly. Okay, so now we have a query here with four records in it. That happens to be the value that I'm looking for, so now I can count the number of records in this field in this particular query, and that'll give me the result I want. So let's go to the main menu, kind of turning the main menu into a little bit of a dashboard here. Let's add a text box right there. Open it up, and in here, let's give it a name. Let's say count missing Q or count missing training. How does that sound? Count missing train, okay. The control source, what's it going to be? I'm going to shift F2 to zoom in so you can see it better. Equals DCount, what's the field? Employee ID or is it count employee ID? Oh no, I changed it sometimes. Hang on, let me take a look. Well, I'll put the star in here for now because this doesn't matter because in the output results, you're only getting one field, so it really doesn't matter here if you put the star. And then the name of the query is employee course missing agggq. Let's take a look and see if it's getting a star. Save that, close it, close it, open it, and there's our four. I'm just curious. This guy here is, no, it's just employee ID because if you do a sum, sometimes it'll say some of employee ID. All right, so the more efficient way to do this is to put employee ID in, right, instead of the star. It doesn't really make that much difference because like I said, you're only getting one field in the output. And now we can just make this a little prettier, right? So we'll slide this over here. We'll say missing training colon and then this doesn't have to be that big, maybe about there. We're going to line more stuff up below it too. This we're going to go format shape outline transparent, shape fill transparent. I'm going to change that to a light blue, and we'll change this to a darker blue but still bright, and we'll bold it, and we will right align that one and left align that one and save it. Close it, open it, and there we go. It's pretty cool as we line more stuff up underneath. It'll all line up right there. Looks pretty cool, huh? And I can see when I open my database up. I got four people. Who's those four people? What do they need? All right, there it is. Now you can get on the horn or you can email them or you can be like, hey, all right, and maybe we'll slide. Okay, good enough.
All right, so next up we're now we know who's missing training completely. You got to go in and add those classes, enroll them, whatever you got to do. Now, what about people who are enrolled, but they're past their deadline to have the training completed, right? They're still in progress. And you know, it's five days later. This will have it done. So let's take a look at, let's go take a look at Jean-Luc here. All right, he's enrolled, he's missing stuff, but he's also enrolled in two classes that are in progress. Let's see, let's make these deadline dates earlier. Today is November 13th, so let's say we only gave him one day to finish. All right, so 11, 12. Sorry, it's Jean-Luc. You should be on this. You should be on the ball here. And we'll make this one, so you had to take that one the same day. Okay, so he's technically overdue on completing those two. Let me make me overdue on one as well. Here's an in-progress for IT103. We'll make that 11, 11. Okay, so there's two employees.
At least, I haven't looked at the rest of them. Now we have our employee course status queue. All right, the course status. This shows all of the courses that each person's enrolled in. Okay, or missing, but we do have the ones that they're enrolled in right there. And you can see who is completed, in progress, whatever. So what I want to do is I want to narrow this query down a little bit but without changing this one too much, right? We can't put a filter on here and say just the in-progress ones because someone else uses this query. That's the problem you run into with these queries sometimes is you've got to be very careful when you modify it that you don't mess up someone else. You know, you don't yuck someone else's yum, right? I don't want to mess up someone else who needs this query.
And if you want to see who else is dependent upon this query, by the way, go to database tools and then object dependencies. And this thing will come up. It'll tell you right here who else requires this. So employee course missing queue requires this query the way it is. All right, I got a whole separate video on object dependencies. Here it is if you want to go watch it. It doesn't check dependencies in your VB code, though. So you could have some VB code that is relying on that query existing too. Although in my VB code, I try to rely on just VB code, but yeah, you know.
So anyways, it's just this one other query now. What I want to do is I want to also in addition to this description of the progress, right? I got the status ID in the description. I want to also put on here the required date. Now, before we modify this query, be careful the way I do it is I check to see how many records it returns right now, 35. Okay, so I'm going to come in here design view and I want to add the deadline date to this query. It should be okay. I don't think this is going to cause any problems, but you never know, so let me add it, and I'm going to run the query again and okay. I got the same number of records. It should be safe. Sometimes if you make this change and you see like five times the number of records appear or a quarter of the number of records appear, you know you messed up something, but in this particular case, I'm just adding another field from a table that's already in this query. So it's probably safe, and then this particular case, it looks like it is okay, so I got the deadline date for this training to be completed. I'm going to save this query just as it is.
And now we can say, give me another query that shows all of these same records where the status ID is one in progress and it's past the deadline date. So while I've got it up here on the screen, I'm just going to do that. I'm going to say it's going to be okay. It's going to be the status ID is one and deadline date is going to be less than today's date like that. So I've just put that in my notepad slide it off the side because now I can make another query create query design bring in the query we were just working with this guy you get out of here. Okay, for this, I'm going to bring in all the fields and then I would need criteria on status ID and deadline date, and I don't want to see them twice. So I'll turn these boxes off, right? Status ID is got to be one. Did, did, did, did, what! Oh? Oh, that's sort the my bad. Sorry right here is down here. Stupid Mustang rookie mistake. All right and the deadline date has to be less than today's date. We'll give them that benefit of the date like that. Okay, save this employee course status overdue. We'll call it hey, they're overdue for this training. All right, and now if I run it there we go. That's exactly what I was expecting to see me and Jean look for two of them. Okay.
Now I'm not going to go through making another report. It's the exact same thing we just did in the last lesson. You just use this for your report if you add your grouping levels and you're sorting whatever else you want to do all right up. We will throw up the throw up a button to open this up if you want to right employee. Let's write it down here, so I will forget it employee course status overdue Q. We'll make an aggregate query for it as well, right? Create query design bring in this guy bring in employee ID get rid of you turn that on and go group by save this. Let's copy that this is why I like to work with the notepad because I can just copy this right that I haven't every type at all time paste it and just put aggregate in here like that run it you should get two right two records. Okay, and now on my main menu we can do something like this. Let's do let's put the buttons below the counts with the counts of copy. I'm going to copy and paste that overdue training, right, and this is going to be that with the aggregate right save it and if you want to make a button all right, we'll do overdue training report do I'm not going to open a report. We'll just open that query do command dot. Oh, yes, I know I didn't name the button open query. Sorry, Alex that thing. Oh, I got a copy again. When you copy and paste objects when you're back here like a copy and paste it a button that'll go on your clipboard. You can't the text is gone. There is a Windows clipboard tool you can get so that you can it stores the last couple of things that you copied you can pick from any multiple one of those all right save that close it close it open it. There we go. We got four missing two overdue who's overdue these guys are you can make that on his pretty report if you want. Okay, looking pretty good.
All right, in the next class then we got to deal with people whose certifications slash training have expired or are expiring soon. We'll do that in the next lesson. In the meantime, if you want to learn more about aggregate queries. I spent lots of time on them in Access Expert Level 11 and in Access Expert Level 29 I cover the aggregate functions. These are useful for like in the footer of a former report and I also cover the different domain aggregate functions like DLookup DSUM DCount. Right the stuff that we're working with in this video series. I spent a lot of time on these functions in my full course. All right, so that's it for part 10. Tune into my role saying bad times saying bad channel and of course, if you remember you can watch it right now because I'm gonna I'm gonna keep going tonight. I got I got my caffeine fix. I'm on a roll. I'm gonna record a bunch of stuff tonight. That's gonna do it for part 10. See you tomorrow for part 11. That's your TechHelp video for today. Hope you learned something live long and prosper, my friends. See you tomorrow.
TOPICS:
Creating an aggregate query
Using the DCount function
Converting query to aggregate query
Adding fields to a query
Filtering query with criteria
Overdue training detection
Creating overdue training query
Creating query for overdue courses
Modifying main menu interface
Adding a text box for count display
Opening a query through button
Making UI elements transparent
Creating employee course overdue query
Using Notepad for query setup
Checking query modification safety
Using object dependencies tool
Aligning UI elements visually
COMMERCIAL:
In today's video, we're continuing with part 10 of our employee training series. We'll focus on displaying the count of employees missing training right on the main menu of our Access database. You'll learn how to create an aggregate query and use the DCount function to tally those missing their training. We'll style our main menu to highlight these crucial metrics visually. Additionally, we'll create a query to flag employees overdue for their course deadlines. This lesson sets the stage for identifying and managing overdue training efficiently. 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 using the DCount function in Microsoft Access in this tutorial?
A. To create a list of all employees
B. To count the number of records in a query
C. To display text in a text box
D. To sort records in ascending order
Q2. Why does the instructor prefer using Employee ID in the aggregate query instead of using * (star)?
A. It requires less memory usage
B. It runs faster when scaled to larger databases
C. It provides more detailed information
D. It automatically sorts the data
Q3. What is the benefit of using an aggregate query as demonstrated in this tutorial?
A. It allows creation of a dashboard layout
B. It enables modification of the existing database structure
C. It lets users sum, average, and group data
D. It makes reports visually appealing
Q4. What should you check after adding a new field to a query?
A. The accuracy of the field names
B. Any changes in the number of records returned
C. The appearance of the query interface
D. The indexes applied to the field
Q5. According to the tutorial, what utility can help identify which other objects in Access depend on a specific query?
A. Query Developer
B. Object Dependencies
C. Query Monitor
D. Dependency Analyzer
Q6. How does the instructor suggest adding a criteria to identify overdue training entries in the query?
A. Set the flag to true in the status column
B. Compare the deadline date to a predefined date
C. Ensure that the deadline date is less than the current date
D. Use a custom script in VBA
Q7. What approach does the instructor recommend when modifying queries that other users or features depend on?
A. Replace the old query with the new one
B. Create a copy of the query before making modifications
C. Ensure all users are notified of changes
D. Modify the query directly without backups
Q8. How are domain aggregate functions like DCount useful in an Access database as explained in the video?
A. They are useful for designing the home screen
B. They allow calculation of data without writing complex queries
C. They provide navigation between forms and reports
D. They help format reports with color themes
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-C; 7-B; 8-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 comes courtesy of AccessLearningZone.com. I'm Richard Rost, your instructor, and we're diving into part 10 of the employee training video series. This marks the longest series I've ever done, and with part 11, we'll be setting a new record. But let's focus on part 10 for now. If you haven't watched parts one through nine yet, I recommend viewing those first.
In our last session, we created a report for missing training. Now, I want a quick way to see the number of employees requiring training as soon as I log into the database. We'll build this feature today.
To achieve this, I initially attempted to use a direct subquery with the DCount function, something common in other databases. However, Access doesn't handle it the same way, so we'll need an aggregate query. An aggregate query groups records, allowing operations like sums and counts. If you're unfamiliar with aggregate queries or the DCount function, I suggest reviewing the relevant tutorials first.
First, we'll create an aggregate query. I'll use the query named "employee course missing queue," which lists employees missing training. We'll focus on organizing these records by employee ID for efficiency, especially if scaling up to a larger system like SQL Server later.
In this query, we transform it into a total query by grouping the employee IDs. We then save it as "employee course missing agggq." This query now shows a simple count of four records, representing four employees requiring attention. Next, let's utilize this data in the main menu, effectively turning it into a dashboard.
We'll add a text box to display the missing training count. The control source uses the DCount function linked to our aggregate query. Upon saving and reopening the database, the number of employees needing training appears right on the main menu.
Moving forward, we need to account for employees who are behind on their training deadlines. We'll examine Jean-Luc, who is enrolled but overdue for completion. Our focus will be on fine-tuning the query "employee course status queue," showing each person's course status. We'll create a derivative query to filter records where employees are enrolled but past their deadlines.
We'll narrow the criteria in this new query to only show in-progress courses beyond their deadlines. Saving this as "employee course status overdue," it reveals overdue records. This can then be represented on the main menu as well, showing instantaneously who is behind schedule. The same method applies to creating another button or report based on these overdue records.
In our next lesson, we'll address employees whose certifications have expired or are nearing expiration. For those interested in more about aggregate queries, I delve into these topics in Access Expert Levels 11 and 29. The courses cover aggregate functions and domain aggregate functions like DLookup, DSUM, and DCount.
That wraps up part 10. I'll continue to create more content tonight, and you can join me tomorrow for part 11. For those eager to learn more, a complete video tutorial is available on my website. Live long and prosper, my friends.Topic List Creating an aggregate query
Using the DCount function
Converting query to aggregate query
Adding fields to a query
Filtering query with criteria
Overdue training detection
Creating overdue training query
Creating query for overdue courses
Modifying main menu interface
Adding a text box for count display
Opening a query through button
Making UI elements transparent
Creating employee course overdue query
Using Notepad for query setup
Checking query modification safety
Using object dependencies tool
Aligning UI elements visually |
