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've got to pay for. All right, we're going to talk about that in today's video. Today's question comes from Dexter in Marietta, 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. The 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 beyond the basics. 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've got to know. Now first, if you haven't watched my invoicing video, go watch it because this is the basis of it. You've got to be able to make an invoice. So go watch it if you haven't watched it yet. This is the database we'll be using today. You will definitely need to know grouping levels and reports, right, because 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've got to know how to use grouping levels, so go watch it. 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. So go watch it 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 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. So a group footer or a report footer works the same as a form footer. So go watch it 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 TechHelp free template. This is a free database you can grab off my website if you want to. And in here, if you watched 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 statement. 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, 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. 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 Ctrl; to put today's date in there. That's a trick from Excel. You can also do Ctrl Shift; to put in the time as well. 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 300. Alright so we got a couple of 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 the statement.

Alright, 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 video. And what this does basically is it gives us this extended price over here, which is basically quantity times unit price. Take a peek at it as a refresher. It's basically my order detail. I'm saying basically too much, aren't I? I just caught that. I'm basically saying basically too often. Here's all our details from each line item, and then we're just multiplying them together to basically get the extended price.

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 U right there. And that join should come between them. 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 is going to happen in a minute here, but just bear with me. All the stuff in the order table and I want the extended price from over here. Okay, and if I run this, you're going to see here is each line item and their extended prices. And you can see here is order one, right, here is order two, order three, and so on. Well, no, order three is 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 in 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 can't use an asterisk. Yeah, 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 customer's information, right, the name and address and stuff. We need the order date, yup, whether it's paid, yup, the description, yup, 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 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. I like to put the price at the end. 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 is on the left, all the detail stuff is on the right. Now, I should be able to aggregate it now. Okay, good. We've got my group by 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. Oops, not average, sum. 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 1's orders. All three of them for me. 1's paid, 2 are unpaid, and those are the totals.

Now this is going to be for billing statements. So I don't care about the paid ones. If you want to have one that's got your statements and shows the paid and unpaid, fine, leave mine here. But I just care about sending them a statement of what they owe, 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, it says sum of extended price. I 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: 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. 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. They normally don't bark in the background of 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. Customer 1 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. Since this deals with orders, I like to keep all the things that deal with orders together. All 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 said I've been doing it that way since I can remember. So it's order, unpaid, Q. Now we've got to add the customer information to this data so that we can bring it into our statement. We're going to need the customer on their statement at this point you can try to add it here but I wouldn't recommend it so this is an aggregate query we've got the exact they don't want their one of the mistakes that I see beginners make a lot as they try to do too much in one query it's a bit of the probably the number one biggest problem that I see with the gators the bright you've got what you hear this is fine save it now we 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, all right, the order unpaid 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 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 gets 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, 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. So there's the customer information that we need for our statement. 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. 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 query 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 actually send to the customer and that's going to look nice. So tune in tomorrow, no not tomorrow, hold on, tune in Monday for part 2, or if you're a member you can watch it right now because 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 TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you Monday for part 2.


TOPICS:
Creating billing statements for customers
Ordering customer database fundamentals
Grouping levels in reports for data management
Building aggregate queries in databases
Usage of form footers in reports
Utilizing a database template for billing
Manipulating date fields in database entries
Developing queries to filter unpaid orders
Summarizing line items to calculate order totals
Implementing customer data into reports for statements
Building comprehensive billing reports for customers