Job History
By Richard Rost
15 months ago
Display Recent Job History for Candidates in Access
In this Microsoft Access tutorial, I'll show you how to display a candidate's most recent job and the one prior, without having to sort through their entire job history. This technique will involve using key functions such as DLookup, DMax, and NZ, without any programming.
Evan from Allen, Texas (a Platinum Member) asks: I run an employment agency and maintain a complete job history for each of our candidates. When viewing a candidate's record, it would be helpful to quickly see their current job and the most recent previous job at a glance. What is the best way to display this information on their record?
Members
There is no extended cut, but here is the file 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, recent job history, candidate job display, job history query, DLookup function, DMax function, continuous forms, table relationships, current job identification, TechHelp template, employment agency database, customer-job link, non-programmatic database solutions, job comparison
Subscribe to Job History
Get notifications when this page is updated
Intro In this video, I will show you how to display the most recent job history for candidates in Microsoft Access. We will talk about creating a job table, defining what counts as a current and previous job, and setting up a form where you can easily see this information at a glance. You'll learn how to link forms, set default values with Forms notation, arrange and align controls, and add command buttons to open related records. This technique works not just for jobs, but also for orders or any other scenario where you need to see a customer's most recent entries quickly.Transcript In today's video, I'm going to show you how to show the most recent job history for candidates in Microsoft Access. For example, you've got their complete job history in another table, and you want to quickly, at a glance, see what their current job is and what their previous job was. This is good for jobs, it's good for orders, it's good for all kinds of stuff. If you want to see the last something and the previous something before that, no matter what you're dealing with, this technique will work for you.
This is going to be an expert-level video. What is expert? Well, expert is a little more advanced than beginner, but it's not quite developer, which means we can do this without any VBA programming at all. We just have to know a couple of functions, although I might sneak a little VBA in toward the very end, but you don't have to know programming to be able to do this.
Today's question comes from Evan in Allen, Texas, one of my platinum members. I'm going to try something new today. Here we go. Ready? I run an employment agency and maintain a complete job history for each of our candidates. When viewing a candidate's record, it would be helpful to quickly see their current job and the most recent previous job at a glance. What is the best way to display this information on their record?
Anyways, Evan runs an employment agency, and he wants to be able to quickly see that person's current job and their previous job without having to load up their entire job history. We're going to build something like this. We've got a customer form. Right here, we can see the current job since that date and their previous job, which they had until that date. Here's their full complete job list. Let me move this back over here. You could put it in there what they did right from this date to that date. No end date means they're still in that job. Previous job, previous job, and you can see these two top ones here go onto that record. Go to the next person. James Kirk doesn't have a current job. He doesn't have one with an open end date, so he's got no job. Although Captain Kirk would never not have a job.
And so on. That's what we're going to do in today's video. Even though this doesn't require programming, I do have a bunch of prerequisites for you that you should know before trying to tackle this video. You should definitely understand relationships between tables. Obviously, we're going to have a relationship between customers and jobs. We're going to make a job table. That'll be a one-to-many relationship. I'm using the term customers generally because I already have a customer table built. But this could be employees. This could be whatever. If you're a placement agency, these would be your customers. So you might have employees and you might have companies. You can have different tables, but for the purposes of the class, we're going to call them customers.
You should know how to open a form to a specific record. Watch this video. You can do it with a wizard. You don't need coding for this. You should know what continuous forms are. You should know what string concatenation is. That's putting two strings together. This is crucial. You should know how to use the DLookup function. Very important. If you don't know DLookup, definitely go watch this video. We're also going to use DLookup's little cousin, DMax. Go watch this one too. Of course, a little companion to DLookup and DMax is always NZ, the null zero function. Very important. Go watch this one. We're going to use the IIf function, immediate if.
As you can see, there are lots of different functions we're going to have to use to do this, but no programming, just functions. You should know how to get a value from another open form using the Forms form name notation. Watch this video if you don't know what that means. I will be using my TechHelp free template for this database. If you have not yet watched, go watch my blank template video where you can also download a copy of this database. I also recommend you watch this video where I show you how to link the customers and the contacts together, which is kind of the same thing we're doing today with customers and jobs.
So you've got all those. Go watch any of those videos if you're not familiar with them. There are a lot of them, but it's all easy stuff. It's free. It's on my YouTube channel. It's on my website. Go watch all of those and then come on back.
Here I am in the TechHelp free template. This is a free database. You can grab it from my website if you want to. In here, we have customers. We're just going to use customers. We're not going to bother renaming it to employees or any of that stuff. There's people. It's people. We're going to track each one of these people's jobs. We need a job table. Let's go to create and table design. We're going to start off with our auto number. Every table should have one job ID. We're going to need a customer ID so we can relate this back to the customer it belongs to. This will be a number of type long integer. A description of said job, short text is fine. Then we'll need a start date. That'll be a date/time and an end date. That'll also be a date/time. You could put whatever other information in here that you want regarding this particular job. You could put a notes field. You could put who their employer was. Whatever you want. That's up to you. I'm just going to keep it simple for class. But anything else related to this job goes in this table. Save this as my job T for table. No primary key defined. Say yes, that'll make this the primary key. Some were all good. Let's put some data in it. I'm not going to make you sit here and watch me type. So I'm going to copy and paste it. I already typed it in my other database. In fact, I'm going to cheat completely. Watch this. I'm just going to delete this job table that we just made. And I'm just going to steal my other one. Look at this. Click drag drop. There we go. There's my job table.
I've got three jobs for customer one. That's me. Lead admiral, shuttle pilot, transporter operator. I've got two jobs for customer two, two jobs for customer three. And then I just put some sample data down here, job A, job B. So we can take a look at the start date and the end date for each of these jobs. Now, some definitions. We're going to define the current job as the job with the most recent start date that doesn't have an end date. So if it's their current job, that means they're still at it. That could be important for a recruiter. They might want to know if the person's currently out of work or if they have a job. So if there's no end date, then the current job is the one with the most recent start date.
Now, their previous job is going to be the job with the most recent end date. So it has an end date. So they're not currently at it. And it's the most recent one that they quit. Because they might have two that are roughly the same, but they might have had one later. So that's how we're going to define these things. Obviously, you can change those definitions if you want, but that's how I'm going to build this database. Let's make a form out of this so that we can very easily work with this stuff.
Now, I already have my continuous and single form blank templates here. I talk about these in the blank template video. So we're just going to copy this continuous form and use it for our job form. Copy paste, Control-C, Control-V. This will be my job F, my job form. And there you are right there. Let's design view. First thing I'm going to do is double click here. This doesn't have to be that big. Now, we're going to set the data record source equal to where you're getting your data from, the job table. Now, if you go to Add Existing Fields, there's the fields from the job table. I'm just going to drop them right down in here. Click on that one. Click on that one with the Shift key down. That'll select them all. Click drag, drop them down here. Now, do you need to see the job ID on here? Probably not. You need it for relationships and stuff later on, but we don't need it on the form. Customer ID. Do we need to see this? No, probably not. But we are going to need it later. So I'm going to actually delete the label. I'm going to slide this guy down into the footer. We'll see why in a few minutes.
Description, start date, and end date. I am going to use these guys. I'm going to delete the labels. I'm going to actually delete these. We don't need those. I'm going to put, let's do start date here and then end date there and then description next to it. Let's see what this looks like. There we go. As far as the labels go, I'm going to use my one label trick. Watch this. I'm going to click on this guy, slide it across the whole thing like that. We're just going to use one label start, like that. A bunch of spaces and a bunch of spaces and description. I think that's easier sometimes for these little forms than making multiple labels in there. That's just my opinion. You can do it too hot. Let's shrink up that detail section because we don't need all that. Leave this guy here again for a second. Save it. Close it. Let's open it. There are everybody and all the data that's fine. Let's left-align these date fields here.
Design view. I'm going to click, Shift click, to select them both. Format. Left align. Then save it. Close it. Open it. That looks a lot better. I can see the tab order needs to be adjusted because this is the first field in the tab order. See what I have. Let's fix that. Design view. Let's click on the detail section. Go to tab order. Hit auto order. Hit OK. If you're not familiar with tab order, that's a beginner topic. I'll put a link to my tab order video down below. One more time. Save it. Close it. Let's open it back up again. Looks good.
Now, let's slide this over here. Let's open up the customer form. Now I want a button on my customer form that I can click on to open this guy. So I'm going to close this. Right click. Design view. Let's grab a button from up top here. Drop it down below. Form operations. Open a form. Next, what form do you want to open? That guy. Next, we're going to open the form and find specific data to display. Next, now we're going to match the fields up. The customer ID on the customer form is going to match the customer ID on the job form. That's why we had to have it there. That's one of the reasons we had to have it there. We'll talk about it more in a second. Hit this little button right there that'll match those two together. Basically, what field links these together? That's the field that makes your relationship. Next, what do you want to have on the button? Jobs is fine. That's just a caption for the button.
Next, you want to give the button a good name. Of course you do. You don't want to call it command 30. I'm going to call it job BTN job button. That's how I name my buttons. And then finish. There's your jobs button. We'll make it match the other two. Resize it just a little bit. Let's make it bold since it's for this class. Save it. Close it. Open it. Ready? Hit jobs. Boom. There's just my jobs. That's what I wanted to see. Just my three jobs. Notice customer ID is one for each of those. Let's go to the next person. Jobs. See how it now. It's just customer two. Just that person's jobs and so on. That relationship is important. And the button code links them together.
Now, why do we need to have this down here? Well, if we go to add a new job for this person, notice how that diverts back to zero. Excuse me, reverts back to zero. We want to make the default value for this box equal to this customer ID. That's why I wanted you to watch that contact management video because I talk about that in more detail in that video. So we're going to right-click, design view. We're going to open up this guy's properties, the customer ID on the job form. We're going to set its default value. I'm going to zoom in so you can see this better. Shift F2. It's going to be equals forms customer F, customer ID. That's how you get that customer ID. Hit OK. Save it. Close it.
Now if I open the form up again and I go to add another job, notice the default value is three now. And it stays in there. That's important. Another thing you might want to do is you might want to make this form modal. What modal does is it makes it so that you can't change what's behind that form before you close yet to close this form first. I got a whole separate video on modal. I'll put a link to that down below as well. I'm not going to leave this modal because in all honesty, when you're developing a database, when you're designing it, when you're working on it as the programmer, it's a bit of a pain to have to deal with modal forms. So I usually go back when I'm done, and then I'll make my forms modal.
Because you don't want someone coming in here and closing this or changing it, and then this value is not right. Now that we've got that set there, we can come in here, design view. I can hide this guy. Now what I like to do when I hide fields, just for me, I come in here and I make them red, like a red background, and then like a white foreground. This is just for me because I'm going to hide this guy. I'm going to make it not visible. I'm going to open it up. I'm going to go to its format, properties, and find visible and change that to no. The value's got to be there so we can set it. It has to be on the form. But we don't need to see it when the form opens up.
So if I close that and open it back up again, right?It's still there, but we just don't want to look at it. But when I switch over to design view, as the developer, I can see that it brings it to my attention that there's a hidden field here. That's why I make it red. That's a Rick trick.
So now that we've got our jobs in the system, we've got a way to update and edit them. Now we're ready to start putting our fields on the customer form so we can see that data at a glance - the current job and the most recent job.
We're going to see how to do that in tomorrow's video. So tune in, or if you're a member, you can watch it right now because I'm going to record it in just a few minutes. Whoops. Someone's beaming in. But that is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Showing most recent job history in Access
Creating job table and setting fields
Defining current and previous jobs
Building job form in Access
Setting form data record source
Using continuous forms in Access
Adding fields to forms in Access
Aligning and arranging form elements
Opening a form to a specific record
Adding buttons to forms in Access
Linking customer and job forms
Setting default values with Forms notation
Hiding fields on a form in Access
COMMERCIAL:
In today's video, we're learning about how to quickly view the most recent job history for candidates in Microsoft Access. You'll discover how to show a candidate's current job and the previous one without sifting through their entire job history. This advanced tutorial keeps things simple by avoiding VBA programming and instead utilizes essential functions like DLookup, DMax, and NZ. We'll set up a relationship between customers and jobs, and craft a form to neatly display this useful information. By the end of the video, you'll be equipped to track and present data efficiently for any scenario. 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 main goal of the technique demonstrated in the video?
A. To sort job history in alphabetical order
B. To display the current and previous job at a glance
C. To calculate the average duration of employment
D. To export job history to Excel
Q2. How does the video define an "expert" level in Microsoft Access?
A. Advanced level requiring extensive VBA programming
B. A step above intermediate, involving some VBA
C. More advanced than beginner, involving no VBA programming
D. Equivalent to developer level, needing deep coding knowledge
Q3. What should be the primary key in the job table according to the video?
A. Customer ID
B. Job Description
C. Start Date
D. Job ID
Q4. Which function is suggested for use to find the most recent job start date with no end date?
A. DCount
B. DMax
C. DMin
D. DSum
Q5. What is the purpose of the Customer ID field in the job table?
A. To store a secondary identifier for the job
B. To relate the job back to the candidate it belongs to
C. To indicate the level of the job
D. To sort jobs based on customer ratings
Q6. Which field is used to open the form to a specific record?
A. End Date
B. Job Description
C. Customer ID
D. Start Date
Q7. What is the format for referencing a control on another form in Access?
A. [Forms]![FormName]![ControlName]
B. {FormName}.{ControlName}
C. (FormName/ControlName)
D. <Form:FormName.ControlName>
Q8. What visual trick does the presenter use to denote hidden fields on a form?
A. Make fields transparent
B. Use a red background and white text
C. Change the font style to italics
D. Apply a bold border to the fields
Q9. What kind of relationship is established between customers and jobs in the database?
A. One-to-one
B. Many-to-many
C. One-to-many
D. Many-to-one
Q10. What is the condition defined for a job to be considered the "current job"?
A. It must have the earliest start date in the history
B. It must have the latest end date
C. It must have the most recent start date and no end date
D. It must be the highest paying job
Answers: 1-B; 2-C; 3-D; 4-B; 5-B; 6-C; 7-A; 8-B; 9-C; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on displaying the most recent job history for candidates in Microsoft Access. Suppose you have a complete job history stored in another table - wouldn't it be convenient to view a candidate's current and previous jobs at a glance? This technique is applicable not only to job histories but also to orders and any other data type where you need to observe recent and previous entries. This tutorial introduces a straightforward method to achieve this without delving into complex programming.
Today, I'm addressing a question from a member who runs an employment agency. The aim is to quickly determine a candidate's current job and their previous job without having to go through the entire history. In our setup, we have a customer form that displays the current job and its start date, along with details of their previous job. For individuals who do not currently hold a job, like in one example where Captain Kirk doesn't have a current job, the form will reflect that absence.
Before we proceed, it's important to have a solid understanding of table relationships, specifically a one-to-many relationship between customers and job history. You'll need to know how to open forms to specific records, familiarize yourself with continuous forms, and understand string concatenation and key Access functions like DLookup and DMax, along with others like NZ (null zero) and IIf (immediate if). You also need to understand how to retrieve values from an open form using the Forms form name notation. While we won't delve into programming, these concepts will help you build this database effectively.
Within our TechHelp free template, we utilize a job table to track job history associated with a customer ID, which in turn relates back to a customer table. The job table includes fields for job ID, customer ID, job description, start date, and end date. The current job is defined by the most recent start date with no end date, and the previous job is the job with the latest end date. These definitions can be adjusted as needed.
We'll create forms to handle this job data. By setting a job form with a continuous format, linking the data from the job table, and organizing fields for start date, end date, and description, we make the data easily accessible. A key feature is adding a button on the customer form to open the job form, displaying only that customer's jobs. The button's logic is built upon linking the customer ID between both forms.
Customizing the form, like setting default values, ensures seamless data entry. Making fields like customer ID not visible aids in keeping the focus on relevant information while maintaining essential backend operations. You may choose to make forms modal to prevent background changes while a form is open, especially after development completion.
Now that the data entry and viewing system is set up, we're ready to display these job details succinctly on the customer form. In the next session, I'll guide you through setting up this display to observe job information directly from the customer form.
For complete video tutorials that walk through every step covered here, visit my website at the link below. Live long and prosper, my friends.Topic List Showing most recent job history in Access
Creating job table and setting fields
Defining current and previous jobs
Building job form in Access
Setting form data record source
Using continuous forms in Access
Adding fields to forms in Access
Aligning and arranging form elements
Opening a form to a specific record
Adding buttons to forms in Access
Linking customer and job forms
Setting default values with Forms notation
Hiding fields on a form in Access |
