Collaborate on Bill Statement Format for Communications & Media with Ease Using airSlate SignNow

Bill statement format for Communications & Media

welcome to another techel video brought to you by access learning zone.com I your instructor Richard Rost today we're going to talk about billing statements in previous videos we learned how to make invoices for our customers well once a month what if you want to send them a statement and say hey here are all the orders you got to pay for all right we're going to talk about that in today's video today's question comes from Dexter in maretta Georgia one of my Platinum members Dexter says I've been using your invoicing database for a while now and it's great thank you very much we invoice our customers net 30 so I need to generate a monthly billing statement showing what they owe can you show us how to do that well Dexter sure this involves a lot of different pieces and I'm going to go over them in this video I Do cover all this in my full course but today I'm going to give you a digested version a simpler version that's easier to put together because sending billing statements can be pretty complicated but at its core it's not that hard so let's first see some prerequisites now today is going to be an expert level class what does that mean well it's it's beyond the basics it's it's not really for beginners but it's not developer level either we don't need any programming or VBA code to do this but there is some stuff you got to know now first if you haven't watched my invoicing video go watch this because this is the basis of it right you gotta be able to make an invoice so go watch this if you haven't watched it yet this is the database we'll be using today you will definitely need to know grouping levels in reports right CU we're going to take a whole bunch of invoices for a specific customer group them together and make a report for that customer and then we can make it for all customers but you got to know how to use grouping levels so go watch this you should know how to make an aggregate query very important that's where you can for example take all of the detail items on an order and aggregate them together to get an order total all right so go watch this and go watch my video on making form footer totals now in the invoicing video I show you how to make a total down at the bottom right in a continuous form to get a total for all of these items it works the same in reports when we put a total in a group footer all right so group footer or a report footer works the same as a for footer so go watch this if you've never done that before these are all free videos they're on my YouTube channel they're on my website go watch any of these you don't seem like you know what that stuff covers and then come on back all right so here I am in my techel free template this is a free database you can grab off my website if you want to and in here if you watch the invoicing video you know we've got customers and each customer can have orders so let's start out by making sure I've got some orders here for my state now I got one order let's update the dates in here this is a little old let's make this one for May 1st today is May 3rd Friday happy Friday folks May 3rd of 2024 so we'll put that in there today we'll say this one's paid and we'll use that so we can make sure it doesn't show up on our statement right okay let's do another one here all right let's update this one let's say this one's from May 2nd and by the way if you don't know I'm hitting control semicolon to put today's date in there that's a trick from Excel you can also do control shift semicolon to put the time in there as well but I just want the date all right so that's from let's make this one from yesterday and this one will be unpaid and it's about 360 okay let's put one more order in here for me let's put today's date in there and we'll put in here stuff and we'll put in some stuff for 150 and then more stuff for I don't know 300 * two all right so we got a couple orders in the system that are unpaid I got three orders and two of them are not paid so those are the two that should show up on my statement all right now we're going to have to put together a couple of queries to get our data to the point where we can use it in the statement now the first thing we need is our order detail queue you should remember this from the invoicing uh video and what this does basically is it gives us this extended price over here which is basically quantity times unit price right take a peek at it as a refresher it's basically my order detail T I'm saying basically too much aren't I I just caught that I'm basically saying basically too often um here's all our details from each line item and then we're just multiplying them together to basically get the extended price right okay so this is good we can keep this we can use this one now next I want to get a list of all of the unpaid orders and then bring in those details so I can get those extended prices okay because I want a list of all of the unpaid orders and what the total of each order is so let's create let me open this up permanently here double click on it create query design I'm going to bring in my order table this guy order T and I want the line item total so I need that query that we just made that order detail Q right there and that join should come between them right order ID over here to order ID over there we can close the table window now now what I want is all the stuff from the order table and I'm going to show you something's going to happen in a minute here but just bear with me um all the stuff from the order table and I want the extended price from over here okay and if I run this you're going to see here's each line item and their extended prices and you can see here's order one right here's order two order three and so on well order three these two okay now what I want to do is I want to aggregate this so that extended price sums up and becomes an order total Okay so let's make it an aggregate query and that's what I was going to say earlier if you're going to make an aggregate query you can't have the asterisk in there at all all right okay not a problem so we have to just get rid of this and just add in the fields from the order table that you want I bring this up because it's one of those questions that students ask me constantly what's this problem I got this I can't use an asterisk yeah if you're using it if you're making an aggregate query you can't use the asterisk so what do we want what Fields do we need for our statement well order ID of course the customer ID because later on we're going to have to bring in the customers information right the name and address and stuff we need the order date yep whether it's paid yep the description yep and don't bring in notes because again with an aggregate query you can't have long text Fields but we don't need the notes from each invoice on the state statement just a description is fine really you don't even need that you just really need the order date and the amount all right now I like to put the price at the end I it's a personal style thing but I like to keep the fields down here in the order that the tables are up here so all the order stuff's on the left all the detail stuff's on the right okay now I should be able to aggregate it now okay good we got my group buy going across these are all going to be the same for each order because they're coming from the order table but what do I want to do with this one this is the line item total I want to add all of these up well not average some I missed it there we go we want sum for that one so it's going to sum up all of the line item totals for each order and now when I run it there you go there's order one and the total there's order two and the total and so on and here you can see customer one's orders right there if I sort this by customer ID for example you can see there's customer one's orders all three of them for me right one's paid two are unpaid and those are the totals now this is going to be for billing state stat right so I don't care about the paid ones if you want to have one that's got your statements that shows the paid and unpaid fine leave them on here but I just care about sending them a statement of what they owe which which orders which invoices are not paid so go back to design view and under is paid we're going to make the criteria false and now when I run it I see just the ones that are not paid and there's my two unpaid invoices there's one right there and there's the other one okay we're getting there we're getting there slowly now this column over here as all aggregate queries do uh it says sum of extended press I don't don't like that so let's use an alias and change that I'm going to come right in here I'm going to zoom in so you can see it shift F2 and I'm going to say order total colon extended price that says sum up all of those extended prices and I want you to call it instead of sum of extended price call it order total that's called an alias and if I run it now there we go we have a nice field called order total don't worry about formatting it at this stage this is just a query we're going to format it as a currency when we get to the report and yes my dog just barked I'm doing it just to see if you guys are awake no I'm kidding someone made a noise in the backyard in the dog ah they normally don't bark in the background on my videos it's normally a safe space but once in a while all right so our query is good we've got a list of all the unpaid orders in our system right customer one has two of them customer 10's got one customer 19's got one all right let's save this query now I'm going to save this as since this deals with orders I have to keep all the things a deal with orders together right so order unpaid q and again another one of my personal things for naming is I try to keep everything singular if I can not always but I try to like notes will always be notes I just because I've been doing it that way since I can remember right so it's order unpaid Q okay now we got to add the customer information to this data so that we can bring it into our statement right we're going to need the customer's name address email whatever information you want about the customer on their statement at this point you can try to add it here but I wouldn't recommend it since this is an aggregate query we've got the exact data we want there one of the mistakes that I see beginners make a lot is they try to do too much in one query it's a it's a the probably the number one biggest problem that I see with beginners right you've got what you need here this is fine save it now we're going to take the data the output from that query and add more information to it okay so let's create another query to bring in the customer details that we want to each order okay so create query design bring in the query we just made right the order unpa Q there it is okay this time we can bring in the star because we're not going to aggregate this and now add into this the customer T okay that will link in all of the customer information with each one of these orders so just bring in the fields that you want from this table that you expect to have on the statement and again another thing that I recommend you don't do is don't bring in the stars from multiple tables you can but I just don't like doing it it just get things get sloppy so what do we need well we don't need the customer ID because it's already over here so maybe bring in first name and last name if you want their email fine address you know city state ZIP all that stuff if you're going to mail it I'm just going to bring in just address for now just to keep things simple for class okay so there's the customer information that we need for our statement okay all right let's save this one same kind of naming convention order unpaid we're going to add the customer details so customer Q that's how I name things okay and if we take a look at it now what do we got here's our order information over here right with the order total and now each customer's information is right next to it over here okay see that so now we can put this stuff on the top of the statement all right so our quer is good this is basically all the information we need right here to start putting together our statement report and we're going to do that in Monday's video coming up in part two we're going to start building our statement report by taking all that query data that we just put together and start formatting it in a nice pretty report that we can have like that we can actually send to the customer and that's going to look nice so tune in tomorrow well no not tomorrow hold on tune in Monday tune in Monday for part two or if you're a member you can watch it right now cuz I'm going to record it as soon as I'm done here same bat Time same bat Channel and that is going to be your Tech video for today I hope you learned something live long and prosper my friends I'll see you Monday for part two if you enjoyed this video please give me a thumbs up and post any comments you may have below I do try to read and answer all of them as soon as I can make sure you subscribe to my channel which is completely free click the Bell icon and select all to receive notifications when new videos are posted want to learn more click the show more link below the video to find additional resources and links YouTube does a pretty good job of hiding it it's right down there see this's part of the description here right the name the videos up here there's a little show more down there right down the bottom it's kind of hard to find but once you click on that you'll see a list of other videos additional information related to the current topic free lessons and lots more and YouTube no longer sends out email notifications when new videos are posted like they used to do but if you'd like to get an email every time I post a new video click on the link to join my mailing list and you can pick how frequently to get emails from me either as they happen daily weekly or monthly now if you'd like to become a paid member of my channel and receive all kinds of awesome perks click on the join button you'll see a list of all the different membership levels that are available each with its own special perks including my Extended Cut videos access to my code Vault lots of VBA source code in there template downloads and lots more I'll talk more about these Perks at the end of the video even if you don't want to commit to becoming a paid member and you'd like to help support my work please feel free to click on the tip jar link your patronage is greatly appreciated and will help keep these free videos coming I got some puppies to feed but don't worry no matter what these free techel videos are going to keep coming as long as you keep watching them I'll keep making more and they'll always be free now if you really want to learn access and you haven't tried my free access level one course check it out now it covers all the basics of Microsoft Access including building forms queries reports and more it's over 4 hours long you could find it on my website or on my YouTube channel I'll put a link down below you can click on and then I mention it's completely free the whole thing free 4 hours go watch it and okay okay a lot of you have told me that you don't have time to sit through a 4-Hour course so I do now have a quicker Microsoft Access for beginners video that covers all the basics faster in about 30 minutes and no I didn't just put the video on fast forward but I'll put a link to this down below as well now if you like level one level two is just a dollar that's it $1 that's another whole like 90-minute course level two is also free for paid members of any level including supporters so if you're a member go watch level two it's free okay want to get your question answered in a video just like this one visit my techel page and send me your question there members get priority of course while I do try to read and respond to all of the comments posted Below in the comment section I only have time to go through them briefly a couple of times a month and sometimes I get thousands of them so send me your question here on the techel page and you'll have a better chance of getting it answered and what while you're on my website be sure to stop by my access Forum we've got lots of lively conversations about Microsoft Access and other topics I have a fantastic group of moderators who help me answer questions shout out to Alex Kevin Scott Adam John Dan Juan and everybody else who helps out on the site I appreciate everything you do I couldn't do it without you be sure to follow my blog find me on Twitter and of course on YouTube yeah I'm on Facebook too but I don't like like Facebook don't get me started now let's talk more about those member perks if you do decide to join as a paid member there are different levels silver gold platinum and diamond silver members and up get access to all of my Extended Cut techel videos one free beginner class every month and some other perks Gold members get all the previous perks plus access to download the sample databases that I build in my techel videos plus access to my code Vault where I keep tons of different functions that I use the code that I build in most of the videos You'll also get higher priority if you do submit any Tech help questions now answers are never guaranteed but you do go higher in the list for me to read them and if I like your question you got a good chance of it being answered you'll also get one free expert level class each month after you've finished the beginner series Platinum members get all the previous perks plus even higher priority for Tech help questions you get access to all of my full beginner level courses for every subject and I cover lots of different subjects like word excel VBA ASP lots of different stuff not just access these are the fulllength courses found on my website you get all the beginner ones in addition once you finish the expert classes you get one free developer class per month so lots of training and finally you can also become a diamond sponsor you'll have your name or your company name listed on a sponsor page that will be shown on each video as long as you're a sponsor you'll get a shout out in the video and a link to your website or product in the text below the video and on my website so that's it once again my name is Richard Rost thank you for watching this video brought to you by access learning zone.com I hope you enjoyed I hope you learned something today live long and prosper my friends I'll see you again soon for