Collaborate on Loan Invoice Template for Administration with Ease Using airSlate SignNow

Loan invoice template for Administration

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video i'm going to show you how to track loan payments in microsoft access today's question comes from bryce in chula vista california one of my gold members bryce says i need to track payments for loans that my company services i have the loan amount start date of the loan and the number of months i don't need full loan amortization as that's already been figured out i just need to generate a list of the monthly payments the amount divided by the number of months and track whether each payment has been made how would i store this in access well bryce your job is actually pretty easy since the amortization has already been done for you i guess the loan company will tell you the total amount of the loan how much the monthly payments are and all that's already figured out for you for everybody else if you need loan amortization i do have other videos on that amortization is where you put the total loan amount in the interest rate and the number of payments and then access or excel will calculate what that monthly payment is for you but bryce already has that information he just has the loan payments and wants to keep track of what's been paid and how much is left so we're gonna put together something like this the loan amount the total number of months and then each payment in here and then when a payment's been made check it off right and we'll calculate the amount paid and what's left all right let's put this together a couple of prerequisites first if you don't know how to work with sub forms the if function immediate if or form footer totals go watch these three videos first i'll put links down below in the description in the links section so go watch those first and then come back all right go watch them right now go on go what are you still here for okay if you're still here then you've watched these three videos all right let's get started okay here i am in my tech help free template this is a free download from my website and go grab a copy if you'd like to we're going to need two tables one for the loan and one for the individual payments two related tables i guess relationship should have been a prerequisite too but if you're working with sub forms then you know relationships right okay so let's create table design we'll start off with a loan id that's our auto number we're going to have a loan amount that's currency of course we're going to have the number of months i'm going to go num month i like to keep everything singular it's another one of my tricks all right that way when i'm i'm working later on with code or or whatever i don't have to think to myself was it none month or no months or number of months just no month just stay consistent and of course you can put any other information in here you want that's related to this loan the customer id if you want to link them back to a customer table uh maybe the start date right of the loan itself whatever whatever else you want to put in your notes whatever all right so save this loan t alright primary key yes okay now create table design this is for the loan payments all right so loan payment id that's the primary key for this field it's going to be related back to a loan id that's a number of type long integer that's the foreign key right it links it back to the loan table the payment date this is for the individual payments now right so this will be a date uh the payment amount that'll be currency and then is paid is this paid yet or not yes no make sure that defaults to no and we'll save this as my loan payment t all right so we got loans we got loan payments all right let's put some data in here so let's open up the loan t let's just put a couple of loans in here all right let's put a loan in here for a thousand dollars gonna be paid back over four months all right starting on let's say seven one okay how about a five thousand dollar loan being paid back over five months that'll be a thousand dollars a payment and you see how this works right let's say there's a twenty five hundred dollar loan and let's say this is being paid back over three months all right oh i forgot my start dates let's go 7 15 on that one and maybe eight five for that one okay all right so we got a couple loans in here save changes sure all right let's set up our forms i got a single form and a continuous form down here that i use as templates so i'll start with the single form i'm going to rename that to my loan f all right design view and if you didn't watch the video where i build this go watch it it's the blank video and if you watch the other videos that means that they had prerequisites of this so i know you watch this one too okay uh let's bind this form to the lone t okay and i'm going to add existing fields let's bring these guys all in drop them down there the only reason i keep these ones around is for this for the format painter like this one i see paint paint paint that's all now i can delete these i like to have a gray one for the id it just signals to the user you can't change that and then i use the white one for everything else all right slide this stuff up over here okay and then maybe a little uh a line left okay save that let's close it open it back up again all right looks good there's my loans now the loan payments are going to go over here in a sub form all right so let's do the sub form that's going to be a continuous form so i'll use this guy right click rename this will be my payment or a loan payment [Music] f and before we set this up let's make a query real quick because in the query that's where we're going to determine whether or not um stuff has been paid so create query design we're going to bring in the loan payment t all right just bring in all the fields and then we're going to make one more field over here that's going to be called amount paid all right if let me zoom in so you can see this better shift f2 if is paid comma payment amount comma zero all this says is show me the amount that's been paid okay so if this item is paid put the payment amount here otherwise put a zero there it hasn't been paid yet this will allow us to easily sum up whether stuff has been paid or not so i know the amount that's been paid and i can calculate the amount that's still due so we're going to save this as my loan payment paid queue and if you run that right now we don't have any payments here yet do we let's put some payments in when we get our form done okay but i want to make the query first so we have to go and reinvent the wheel all right so loan payment f right click design view let's bind this guy to that query that we just made so record source is going to be the loan payment paid queue okay all right let's add existing fields we don't need either ids in here why because as a subform the relationship is going to be made by the subform control itself the link child fields link master fields alright so we don't need either of those so let's bring in these three fields right here payment date payment amount and is paid don't worry about amount paid drop those right there we can get rid of these chop off these labels slide the payment date over there slide the payment amount right there put the check box there for whether it's paid or not play with the labels a little bit right we got the payment date and the amount and paid okay close that up shrink that up shrink that up close that save it let's take a look at what we got all right looks pretty good let me uh i like the left align stuff that's just my thing all right here we go left align all right save it close it okay now this i don't like the date picker in here let's get rid of the date picker how do you turn that off double click and then under format i'm going to turn the show date picker off because we're just going to type in dates okay all right here we go loan f we're going to put this sub form right here so design view grab the sub form click drag drop see that delete that guy all right and of course we did all this in the subform video remember that all right open that up give that a little bit more room save it close it open it back up again and there we go so now we can put in our payments so here's loan one right we got a thousand dollars over four months that's 250 a month right so on seven one that's going to be 250. let's say that's paid okay eight one was the next one another 250 and that should be paid right all right nine one two 250 not paid 10 one 250. okay now let's put our totals down here on the bottom let's go back over to the sub form again it's easier to you can do design view with it in subform view inside the other one but i don't like doing that so come over here we're going to copy this guy copy and click on the form footer and paste there you go all right so there's the total i'm going to bold that one in fact where's the format tab format bold come over here go to the all tab this is going to be equals the sum of payment amount sum payment amount okay let's see what that looks like save it close it open it back up there's the sum and and when this is a sub form you'll only see the payments for the loan that you're on all right now how do i calculate what's paid well remember that query column that we made right we just add up amount paid see two of them are paid now look what's going to happen here right that if function makes those zero because that's not paid yet watch this click see that's how that changes that value there with that if function so i want you to watch the if function first all right and this is the form footer totals down here so let's do this guy copy paste and i'm going to unbold it now because i don't want that one in bold are these all on the grid let's let's see right click size to grid i like everything being on the grid yeah okay much better uh you what is it amount paid sum of amount paid right some i can't type today some amount paid save it close it open it back up again and look at that some of my paid 500. and now we can very easily calculate what's due right copy this guy copy paste and it's just a difference of these two it's some payment amount minus some amount paid right equals some payment amount minus some amount paid and this would be uh some do save it and let's put some labels out here copy paste and let's slide this over all right total copy paste slide you right over there paid and the reason why i'm going copy and then clicking on that one going paste is it it attaches it to that text box that becomes an attached label now right and then do okay save it close it let's open it back up inside the loan form look at that see mark that guy paid now notice that's still dirty right you got to leave that record and then it updates the calculations on the bottom okay see yes you can put an event in there so as soon as you click on it it recalculates that that requires one line of code let's put another loan in now go to the next loan all right five thousand dollars over five months start again seven fifteen all right seven fifteen okay thousand dollars a month paid eight fifteen thousand dollars that one is not paid yet nine fifteen thousand dollars ten fifteen thousand dollars eleven fifteen oh and i goofed up i have tab too far there's another thousand dollars all right okay this one all right well they tell you for the payments that comes out to eight hundred thirty 833.33 with a remainder right so on eight five you got eight thirty three point three three actually probably do usually they do three four the first month right let's say that one's paid and then nine five eight thirty three dot three three and then ten five eight thirty three that three three let's see if that adds up yep that's that's good sometimes they put that extra penny in the last payment i've seen them usually put it in the first payment though okay and you got to be careful about that members what we're going to do in the extended cut is i'm going to make you a button right here you're going to click the button it's going to fill all those payments in because if you only got three or five even it's not a big deal if you're doing a you know a 30-year mortgage and you've got 12 times 30 payments to put in here you don't want to put them all in by hand so we're gonna do it with a loop in the extended cut but for the rest of us be careful make sure that your total down here equals that total up here all right here's a little bonus for you let's say now you've got these different loans in here you want to get a quick summary of all of your loans right how much the total loan amount is and how much is still due to be paid on them well for that we can use an aggregate query i didn't put this in the prerequisites because i wasn't planning on doing it but let's do it real quick all right create query design if you have never done an aggregate query here let me add it to the list there you go it's on the list pause this video again go watch the aggregate queries video go on go watch it come on back okay all we have to do is bring in the loan t bring in that loan payment paid queue so we have the amount that's paid for each loan right okay now i'm going to change this guy to a left joint that way all of my loans show up because even if they if they don't have any payments they won't show up in this query you want to still see those right all right so bring in the loan id the loan amount whatever other information you want out of here okay then bring in the amount paid all right now if you run it like this you get that but we don't want that we want to aggregate this we want to group these together and sum these up so you turn on the totals over here and then we're going to sum this column up like that and now you can see each loan and how much has been paid and we can change this guy instead of amount paid well yeah we'll leave them out paid it'll say some of them up hey that's fine you want to add over here the amount due right amount due is going to be the loan amount minus the amount paid just like that and make sure you change this also to a sum all right run it and there you go is the amount due for each load and you can put the customer's information whatever else you want here you know so you can see it nice little summary chart in fact i'll save this as my loan summary queue all right so there you go save changes yes open that up perfect and now we've got the loans all in the system all the payments are in there tracked whether they're paid or not and a nice little summary query right there okay okay stop the press hold on hold on all right i posted a copy of this video and immediately i got a bunch of emails and posts in my forums that that is definitely a wrong answer sometimes when i'm recording these videos i get all caught up on what's going on and talking to you guys and i didn't even look at the numbers here yeah of course this this this amount here is definitely wrong so let's fix it now unfortunately we're going to need another query because at this point here see the problem here i'm adding up this difference which gets pretty big and it adds it up for each line item so since this is an aggregate query we cannot put it in here so delete that save it and now you've got just this okay now we can use this sum of amount paid and subtract that from this but we need another query so loan summary 2q uh save changes to that yes create another query queries loan summary right now we can bring in the loan id the loan amount i'm going to change this to say just amount paid all right now amount due is going to be loan amount minus amount paid and this hopefully should look a lot better okay that's how much you have to people in the forums are giving me a hard time and i will save this as uh let's call this loan summary 2q okay so as you can see when i'm wrong i admit it sometimes i get caught up in the video and talking to you guys and presenting i don't even take a close look at the numbers i've done this stuff a million times i just i goofed all right i messed up i will always at least uh tell you guys in the comments so always check the comments by the way for all my videos just in case because i don't always go back and record another video to mention it there's been a couple other videos where there's minor little mistakes i i make them i'm not perfect well most of the time i am but no okay enjoy want to learn more in the extended cut for the members we're going to make a button to automatically add that list of payments for you not too bad typing four of them in but if you got 40 of them right or a 36 month you know loan you're paying back i don't want to sit here typing in 36 times so we'll make one button click on it boom it'll put all the payments in for you and you gotta watch the rounding errors right superman three office space there's a little two cents extra on that last one gotta be careful about that okay we'll make a cross tab query to summarize the months so you can see each month and all of your loans and what payments should be coming in right so you can track your income and then we'll export that sheet to excel right take this one button click right here boom send it out to excel that's all covered in the extended cut for the members 26 minutes long silver members and up get access to all of my extended cut videos not just this one all of them there's getting close to 200 of them now there's lots and lots of stuff to watch all right and gold members can download these databases that i build in the tech help videos how to 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 the extended cut tech help 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 tech help 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 tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like 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 now if you have not yet tried my free access level one 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 and if you like level one level two is just one dollar and 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 tech help page and you can send me your question there click here to watch my free access beginner level one course more of my tech help videos or to subscribe to my channel thanks for watching this video from access accesslearningzone.com