|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Split Commissions By Richard Rost 
5 years ago
Split Commissions Between Sales Reps per Job In this video, I'll show you how to take a sale and divide up the total amount of commission between any number of employees. Ryan from Georgetown, Guyana (a Platinum Member) asks: I work for a fishing company. I need to be able to take the total amount of fish in each haul (by weight) and calculate the amount to pay each fisherman. They all get equal shares, but the number of fishermen for each haul may be different. MembersMembers will learn how to automatically recalculate these figures without having to manually click on the refresh button. Also, we'll see how to give each rep a different percentage commission.
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!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, split commission, split commissions, splitting commission, calculate, calculating, calculator, agent, broker, sales rep, real estate, job, commission rate, sales commission split calculator, split calculator
IntroIn this video, you will learn how to set up a system in Microsoft Access to split commissions or payouts evenly among employees for each job or transaction. I will show you how to create the necessary tables, set up many-to-many relationships using a junction table, build data entry forms with subforms for assigning multiple employees to each job, and perform calculations to determine individual payouts based on the total value and number of participants. This example answers a viewer's question about dividing fishing hauls, but the technique can be applied to any situation where a commission or amount needs to be split among a variable group.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, you're going to see how to split commissions between employees per job in Microsoft Access. Today's question comes from Ryan from Georgetown, Guyana, one of my platinum members. Ryan says, I work for a fishing company. I need to be able to take the total amount of fish in each haul by weight and calculate the amount to pay each fisherman. They all get equal shares, but the number of fishermen for each haul may be different. Well Ryan, whether you're dealing with fishermen and fish, or you're dealing with salesmen and commissions for a sale or real estate agents or any of that stuff, the technique is the same. You're taking a certain number of people and dividing up a commission or a sales amount, or how much to pay them based on the number of people that you have. Also, we're going to use a, let's call it, job table. Each haul will be a job. Then we'll assign fishermen, let's call them employees or whatever, to that job. So whether you've got sales reps sharing a commission on a sale or a fisherman sharing a commission for a fishing haul, it's all the same techniques. Let me show you how I would set this up. Here I am in my blank TechHelp free template. You can grab a copy of this on my website if you want to. It's absolutely free. I'll put a link in the download section down below. Now I've already got a customer table. It's got a list of people in it. So we'll just pretend these are my fishermen. All right, it's customer T. You can call it employee T, you can call it fisherman T, whatever you want to call it. I've got customer T. Now, what I'll do is make a job table and the job will represent each haul. Then you can relate to that using a many-to-many relationship, exactly how many fishermen were on each job. If you don't know how to set up a many-to-many relationship, go watch my many-to-many video. I'll put a link in the link section down below. Very important, you've got to understand this relationship. If you don't know one-to-many relationships, go watch that video first. There's a link to that one on this page. Basic relationships right here. Go to my relationships video first. If you don't know about relationships, many-to-many relationships is where you can take many things on one side and relate them to many things on the other side. For example, we've got a list of fishermen, a list of employees, and a list of jobs, which for you will be the fisherman's haul - each haul is a job. We have to relate them together. That'll use a third table, a junction table. Go watch the many-to-many video if you don't know how to do this yet. We've got our customer table that represents the fisherman. Now, let's go to creating a new table, table design. We don't need that big of a property sheet there. We're going to use a job ID. That'll be our autonumber. We're going to have a job date, or date/time, whatever you want to put in there. For you, I'd suggest you have probably total pounds for the total amount of fish that was hauled. That'll be a number. You'll probably want to make that a double unless you do whole pounds. If you do 455 pounds or whatever, if you want to have it a fractional component, then make it a double, default maybe one. Then the price per pound, I'm assuming, is how you do it, a currency value, so it's $15 a pound or whatever. I don't know the fishing business. Sorry, I would just guess. I've bought a lot of fish, I've cooked a lot of fish, I've done some fishing, but I don't know the fishing business. Then what I would do is do an is paid when you actually pay out the commission for each job, when you cut the checks, or you move them over to a different table. Mark this one as paid, so your default value we know, and then you don't have to worry about this one anymore. We'll save this as my job T, my job table, it's someone just being done. Let's close that. Now, the job table will have information about each haul in it. So the job table you might have, okay, April 1st, we got a thousand - actually, let's keep it round, a hundred pounds at a dollar per pound. That way it's easier to do the math later. So 4/2, let's say we've got ten pounds at two dollars per pound. Now we have to have a way to track all of the fishermen or the sales reps, whichever way you're looking at it, for each job. If you're doing, like, commissions for a house, you'd have all the realtors that are involved with selling this house. Or if it's an order, you can have the number of commissions sales reps that are involved with that order. That's what this next table is going to be, the junction table. We don't need the main menu. So, create, this will be our junction table now. Create table design. Now the ID, I like to put it like this, it's going to be job X customer ID. That's the autonumber. We have a number for this table, it's kind of meaningless, you're probably never going to need to use it, but I like to have an autonumber in each table. Why? I've got an autonumber video, go watch that, I'll explain why there. I've already spent a lot of time on why I put an autonumber in every table. Yes, put whatever table name you've got here, if it's employee T, if it's sales rep T, if it's fisherman T, whatever. So job X whatever, I'm relating jobs to customers. Now we need to have an ID to relate to each other table. Job ID here. Now it's going to be a number of type long integer. That's a foreign key, which we know that from our relationships video. To make the other relationship we need the customer ID. That's how I know how to track which job has which customer on it. See that? That's all you need. Save it. This will be our junction table, so job X customer T, just like that. Close that. In my other video, I give you a nice little chart here that shows, okay, this is how the customer is related to the vehicles, and this is the junction table, an ID to this table, an ID to that table. You watched this already, you went and watched this, good little students. Now we've got our tables all set. Now we can set up some forms. Let's set up a job form. One of the reasons why I like using my template is I've already got these blank forms over here, so I'm going to take one of the single forms and make this my job form. So copy, paste, this will be my job F, job F here. Open it up, design view, we're going to make this guy point to the job table, so the record source is going to be job T. This could be the job ID, so control source over here, job ID, which we pretty much don't need, but that's okay. Now we got the other field, let's go to add existing fields, we grab money here, job date, total pounds, price per pound, is paid, shift, click, and then drag them all over, drop them there. Pretty much the only reason why I keep this guy around is to use the format painter. Double click, and then go click, click, click, click, there, just copy all those formats over, so I like to keep it around, so now you can go away. We'll drag all this stuff up here, and we'll try to make stuff look pretty like this. Slide that up, slide that up. We'll drag this one up here, we'll put this one up here. So this is basically the information for the entire job. We'll do a little sized grid, looks good. What we want over here is a subform where I can pick the fishermen for each haul, or the employees - in our case the customers - for each catch, for each job, haul, job, fishermen, catch. All my terms are going crazy here. It doesn't matter, it's all Legos. I use this analogy a lot in my videos. Access is like building Legos. You can put them together in whatever combination you want. My job is to show you how the different pieces work, you put it together for your business, whatever you need. Maybe do a little left justify here too. Let's close this, take a peek at it. There we go, there's job one, there's job two. Now let's make the subform. Let's do it as a continuous form, so I can just pick from a list of options. Copy, paste, this will be my job X customer F. Right there, design view. We don't need the ID, and honestly we're going to probably replace this with a combo box, so I can just pick from a list. Delete that. Let's change this to say customer, and slide you over here. Let's change the record source for this form. This is going to be based on our junction table. This guy gets the data from the junction table. Now, we don't need the IDs on here, because it's going to be a subform, and the subform will control setting those IDs. It'll set the job ID based on the parent ID. We do need to be able to pick the employee in here - the customer. Now, I could go to add existing fields and bring over the customer ID, but I want a combo box. I already have a combo box built, don't I? If I go to my main menu, I've already got, on my order form, I've already got this guy. This lets me pick a list of customers. I'm just going to steal this thing. If you want to know how I built this, go watch the invoicing video. I've got tons of videos on making combo boxes, again, prerequisites. You can't be a doctor without taking pre-med in medical school, so I assume you've watched all these other videos, and they're all free. Go watch them, I'll put a link down below. Copy that combo box, and let's go back over here, and we're going to paste it right there. There it is. All this does, it lets me pick a customer from a list of customers, and it's going to save that in the customer ID field, which is a field on the cross-reference table. See how that works? We can shrink that up like that, save it, close it. Let's take a peek at just that form. I can pick a customer - pick a customer, pick a customer. Pretend these are your fishermen. But they're not getting assigned job IDs. If you look at the table, take a peek at the table, look, job IDs all zero. That'll get set by these subform relationships. Let's delete these. Goodbye. Now we're going to take our job form. Design view. We're going to take our job X customer form and drop it, click and drag, drop it right there. I cover this in my subform video. I'll put a link to the subform video down below too. Lots of stuff you need to know before you do this. They're all free videos, go watch the subform video. I can get rid of that label. I can slide this over here like this, make this a little bit bigger. Save it, close it. We don't need the main menu. Open up the job form. Now here's job one on April 1st, 100 pounds at a buck a pound. Put the sales reps in here, the fishermen that were on this job. See that? So Jim Kirk, Malcolm Reynolds, and Will Riker were on this catch, they're going to get paid out of this 100 bucks. We'll do the calculations in just a second. Next, here's job two, ten pounds, two bucks a pound, just Deanna Troi, one person. She should get all 20 bucks. See how that works? Now we've got our data set up, sometimes that's the hardest part, getting your data set up correctly. Now the data is in the system correctly, the next step is to perform some calculations. The first thing I'm going to do is calculate the total value of this haul. This one's 100 bucks, this one's 20 bucks. So we'll put a calculated value here. You could put the calculated value in a query underneath this form if you want to. In this particular case, it's not necessary, we can put it right here on the form. Design view, I'm going to slide is_paid down a little bit. Copy and paste this guy. This is going to be the total value. Open up the properties, total value is going to be the name, total value, and what's the control source? It's going to be the price per pound times the total pounds. I'm going to make that gray, that indicates to the user that they can't change it, that's a calculated value. Save it, close it, open it back up. 100 bucks, 100 pounds, dollar a pound, 100 bucks. If I change this to 200, that gets updated. Let's put it back to 100, keep it nice and even for now. Go to the next one. Ten pounds, two dollars a pound, twenty bucks. Now in here we need to know how much to pay each fisherman, how much to pay each rep. In the footer of the subform, we'll put a count so we can count the total number of them, then divide the total value by that count, if they're all getting an even share. Let's close this, let's go back over to here, design view, let's put this stuff in the footer down here, make this a little bit bigger so we can see everything. Let's grab a text box, drop it down here, and let's copy this format just so we can see it, format painter, there we go. This will be the count. Let me slide this over, like that. We don't need to make this that big, that's good. This is going to be my total count. What's the control source? =Count(). The count function. Now, you can count a single field if you want, like you can count customer ID or whatever, but I'm just going to count all the records, however many records there are, there's only one value that goes in there, so just like in queries, just put an asterisk in there inside of quotes, Count(*). I'll zoom in so you can see that here, right there, Count(*), inside of quotes. That just says count all the records, however many records there are. Save it, close it, take a peek, open it up. Here it is as a form without being a subform, there are four total records. Design view, now take the total value from the parent form and divide it up. So let's copy this, copy, paste. We're going to put in here 'pay each' like that, and this is going to be, all right, pay each. =Forms!JobF!TotalValue / [TotalCount] You could put the count function here again if you want to, but I've already got it as a field, TotalCount, just like that. Here, I'll zoom in so you can see it, there it is. Close this. Now, this won't work here because it has to be a subform, so it can see the value in the parent form. So close that, save changes, open up the parent form, and there you go. Yeah, we got 33.33 because we got 100 divided by 3, someone's got to get that extra penny. That's a whole different class - now we're getting into the Superman 3 problem with rounding that penny. For now, I'm just going to fix that by formatting this as currency, and let's right justify both, left justify this stuff here. Save it, close it, open it back up again. Looks a little more professional. Who gets the penny? I want to figure that out in a different class. I'm not doing it right now. I do cover that in my full classes. Let me just put it this way, it's not simple math, it involves some VBA. You have to figure out if it's an odd number, you have to add up this, multiply this by this, figure out if it's the same as that, if not, you have to add a cent to somebody. How do you figure out who to add the cent to? That's totally up to you. Is it possible? Yes, it's possible. If you guys really want to see how to do it, let me know, and I'll put another video together. Let's go check on the other haul, there's Deanna. Only one of her, one count, and she gets all 20 bucks. So that in a nutshell is how you set it up. We got the data all set up, our calculations are all good, everything works fine, that's how you do the basic calculations. Now, the problem comes in that as you're editing this stuff, you have to manually remember to hit the refresh. If I add a second fisherman to this one, it doesn't refresh unless you leave the record and come back to it, then it updates. Or you hit the refresh button. That involves some programming: manually updating these counts. I think if we put it in over here, let's see, let's do some calculations outside that, yeah, it still doesn't update, you have to leave it and come back to it, then it updates. So that requires some event programming to update it without having to hit the refresh button manually, or you have to put in some VBA code. I will cover that VBA code in the extended cut. I will also talk about what happens if each of these people get paid different amounts. For fishermen, they might get different shares. One person gets two shares, one person gets three shares, etc, based on seniority. I've seen that. Or if you're dealing with other kinds of commission, like real estate commissions, each realtor might get a different percentage. I will also cover that in the extended cut. Before I forget, I was in the middle of recording the extended cut and I thought about one more thing for you. There are two things I want to mention. First, there is a possibility, if you don't do this right, if you go to a blank new job and start adding customers, which in this case will be fishermen, then you might have them in here with zero for the job ID because you don't have a parent record. That's called widow and orphan protection. You've got orphaned records that don't have a parent. I've got a video on that, called Preventing Orphans and Widows. I'll put a link down below. Also, you might have a situation where you put the same fisherman in here twice, and he will get paid twice, but you might not recognize it, especially if you got like 50 fishermen in here. To do that, you could set up something called a composite key, where you prevent the same person from being in the same dataset twice. Again, I've got a video on composite keys, go watch that one as well. I'll put links in the link section down below. In the extended cut, I will show you how to perform those automatic recalculations, so anytime you change anything like total pounds or add a rep, it will automatically recalculate the totals for you. Also, I will show you how to calculate a different number of shares per rep, so if you want to give two shares to this person, one share to that person, or if you want to do percentages, just make sure the number of shares equals out to 100, and that will give you percentages. We'll calculate the total value per share and then figure out the pay for each employee based on that. Then we'll add an 'add rep' button, so you can click on that and it will add a blank new rep to the end of the list. That way you don't have to see that blank row on the bottom all the time. All that's covered in the extended cut, 16 minutes long. Silver members and up get access to all of my extended cut videos. Gold members can download all the templates of the TechHelp stuff, so enjoy. How do you become a member? Click the join button below the video. After you click the join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming as long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar. It's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the purpose of creating a junction table in the scenario described in the video?A. To store additional information about employees B. To relate many fishermen to many hauls (jobs) C. To calculate payroll taxes D. To track the price per pound of fish Q2. In the example, what is the main table used to represent each fishing haul? A. Customer table B. Job table C. Sales table D. Commission table Q3. Why is it important to understand many-to-many relationships before setting up the commission-splitting system? A. Because you need to create only one table for all data B. Because you need to relate multiple fishermen to multiple hauls C. Because Access does not support one-to-many relationships D. Because it determines the form design automatically Q4. What data type is recommended for storing the total pounds of fish if you want to allow for fractions? A. Integer B. Short Text C. Double D. Currency Q5. What is the function of the 'Is Paid' field in the job table? A. To store what time the job was started B. To track if the payout for the job has been completed C. To calculate the number of fishermen involved D. To indicate if the job was successful Q6. How is the 'total value' of a fishing job calculated in the Access form? A. Number of fishermen times price per pound B. Total pounds times number of fishermen C. Total pounds times price per pound D. Price per pound divided by total pounds Q7. What type of Access form is recommended for picking multiple fishermen (or employees) for each job? A. Datasheet view B. Single form C. Continuous form subform D. Modal dialog form Q8. What does the Count(*) function in the subform footer calculate? A. The number of hauls in the job table B. The number of customers in the customer table C. The number of employees assigned to the current job D. The total weight of the fish hauled Q9. When dividing the total commission equally amongst employees, how is the amount to pay each person calculated? A. By dividing the total value by total pounds B. By dividing the total value by the number of employees C. By multiplying price per pound by number of employees D. By dividing the price per pound by total pounds Q10. What issue can arise if you add a customer to a new job record before saving the job itself? A. Customers will receive double payment B. The customer will get a random commission C. The customer record will have a job ID of zero (creating an orphaned record) D. The customer will be assigned to every job in the table Q11. What is a composite key used for in this context? A. To relate jobs to prices B. To prevent the same employee from being assigned to the same job twice C. To calculate pay for each employee D. To filter the jobs shown in the form Q12. What must you do currently to update the calculated values after adding or removing employees from a job? A. Click the refresh button or leave the record and return B. Save and reopen the database C. Add a macro to the report D. Run a query in the background Q13. What advanced features are promised for the extended cut of the video? A. Customizing the look of the forms only B. Automation of recalculations and handling different pay shares C. Printing paychecks D. Exporting to Excel only Q14. If you want to prevent orphaned records in your database, which video does Richard recommend? A. The combo box video B. The relationships video C. The Preventing Orphans and Widows video D. The autonumber video Q15. Why is it important to handle the assignment of extra pennies when dividing commissions? A. Because Access will not allow decimals B. To ensure fair and accurate payouts C. To meet government regulations exactly D. Because Access cannot perform division Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-C; 11-B; 12-A; 13-B; 14-C; 15-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. SummaryToday's video from Access Learning Zone covers how to split commissions among employees or team members on a per-job basis in Microsoft Access. The need often comes up in different industries, whether you are running a fishing operation where the catch needs to be divided among fishermen, distributing sales commissions among agents, or sharing earnings among workers in other types of jobs. The idea is always the same: you have an amount to split and a variable number of people who should share it, often in equal portions but sometimes in custom ratios.Using the scenario submitted by Ryan from Georgetown, Guyana, I discuss the design and setup necessary to accomplish this in Microsoft Access. Ryan needs to take the total fish caught in each haul, measured by weight, and automatically divide the payout among the fishermen who participated. The number of employees per haul can change with each job, so the solution must be flexible. To create this in Access, you first need to set up your tables. Start by making a table for your workers, whether you call them customers, employees, or, in this case, fishermen. This foundational table tracks who is available to be assigned to jobs. Next, you set up a job table, where each record represents a specific job or, in the fishing company example, a distinct haul. This job table should include fields such as the job's date, the total weight brought in, the price per pound, and a flag to indicate whether the job's payout has been processed. To record which employees worked on each job, you need a many-to-many relationship, which is implemented using a junction table. This table connects jobs to the employees assigned to them. If you are unfamiliar with many-to-many relationships or foreign keys, there are additional resources on my website that explain these database concepts in detail. The key is that each junction table record will specify a job ID and an employee (customer) ID, thereby keeping track of each individual's involvement in each job. Once the tables are in place, you can build forms to make data entry and review more user-friendly. The main job form displays details such as the date, weight, price per pound, and whether the job payout is completed. Alongside this, a subform lists all the employees assigned to that particular job. This setup lets you select workers for each job from a dropdown list, thanks to combo boxes that reference your employees table. After you have entered the relevant data, the next task is to perform the actual calculations for payouts. On the job form, you can add a calculated control to display the total job value by multiplying the total weight by the price per pound. In the subform footer, you can include a count of the employees assigned to that job using the Count function. To determine each worker's share, you divide the total job value by the number of workers, and display the result in another calculated control. A common issue you might encounter is that adding or removing employees from a job sometimes fails to update the employee count or recompute the payout amounts immediately. Typically, you have to refresh the form or leave and return before the calculations update. This can be resolved by adding some VBA event code to automatically recalculate whenever relevant data is changed. In today's Extended Cut, I show you how to implement such automatic recalculation with VBA, ensuring the totals are always accurate as soon as any value changes. I also cover more advanced scenarios, such as splitting payouts based on custom shares. For example, if some workers should get more than others because of seniority or a negotiated percentage, you can set up your database to handle those variations as well. This involves adjusting your junction table and control calculations to track and apply the custom share or percentage each worker should receive. Another consideration is data integrity. You want to prevent orphaned records, where a junction table entry refers to a non-existent job, and avoid duplicate assignments where an employee is accidentally listed more than once on the same job. Setting up proper relationships, enforcing referential integrity, and possibly using composite keys will help safeguard your data. Additional videos on these topics are available on my website. In the Extended Cut, I also demonstrate how to calculate share-based or percentage payouts, show how to create a button to add a new rep directly to the list, and address issues like fractional cent rounding (otherwise known as the Superman 3 problem). For those who are interested in deeper learning, Silver members and up on my site can access this and all other Extended Cut videos. Gold members get the added benefit of being able to download all of the sample database templates used in my lessons and access my code vault. Platinum membership offers even more, including access to my full-length beginner and select expert courses in Access and other Microsoft Office applications. You can continue to watch my free TechHelp videos as long as you find them helpful. If you have a question you would like answered in a future video, please visit my TechHelp page and submit your inquiry. Finally, if you are new to Microsoft Access, I highly recommend you check out my free Access Level 1 course. It covers all the basics and provides a solid foundation for learning how to build databases in Access. A complete video tutorial, including step-by-step demonstrations and all concepts discussed here, is available on my website at the link below. Live long and prosper, my friends. Topic ListSplitting commissions among employees per job in AccessCreating a job table to represent each haul Setting up a customer table for fishermen or employees Designing a many-to-many relationship using a junction table Creating a junction table to link jobs and customers Building a job form linked to the job table Adding job details: job date, total pounds, price per pound, is paid Creating a subform for assigning employees to each job Configuring the subform based on the junction table Using a combo box to select customers in the subform Linking the subform to the main job form Displaying assigned employees per job in the subform Adding and formatting calculated fields on forms Calculating the total value of each job dynamically Displaying total number of employees assigned (Count function) Calculating equal pay for each employee using form values Formatting calculated controls as currency Demonstrating behavior if an employee is assigned to more than one job Discussing issues with orphaned records in subforms Mentioning the use of composite keys to prevent duplicate entries in junction table |
||||
|
| |||
| Keywords: TechHelp Access split commission, split commissions, splitting commission, calculate, calculating, calculator, agent, broker, sales rep, real estate, job, commission rate, sales commission split calculator, split calculator PermaLink Split Commissions in Microsoft Access |
