Billing Statements
By Richard Rost
2 years ago
Create Billing Statements for Unpaid Invoices
In this Microsoft Access tutorial, I will show you how to create billing statements for unpaid customer invoices using grouping levels and aggregate queries, without needing VBA code. This expert-level class builds on previous lessons about invoicing.
Dexter from Marietta, Georgia (a Platinum Member) asks: I've been using your invoicing database for a while now, and it's great. We invoice our customers NET30, so I need to generate a monthly billing statement showing what they owe. Can you show us how to do that?
Members
There is no extended cut, but here is the database 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
Up Next...
Recommended Courses
Keywords
TechHelp Access, billing statements, unpaid invoices, customer invoices, net 30 billing, monthly statement, invoice database, order management, Access reports, grouping levels, aggregate queries, query design, line item totals, unpaid orders query, customer details in Access, statement reports creation, Access without VBA, expert level Access tutorial, simple database solutions, order total calculation, Access TechHelp
Intro
In this video, we'll talk about how to create monthly billing statements for your customers using Microsoft Access. I'll show you how to use your existing invoicing database to generate statements that show which orders are unpaid, using aggregate queries to calculate order totals, and grouping levels in reports to organize your data. You'll learn about updating order information, filtering out paid invoices, and combining order and customer details for clear, effective billing statements. This is an expert-level session, so some familiarity with queries and report grouping in Microsoft Access is recommended. This is part 1.
Transcript
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
Quiz
Q1. What is the primary purpose of sending a billing statement to customers?
A. To notify customers about new products
B. To provide a monthly summary of unpaid orders
C. To request immediate payment on all past transactions
D. To update customer information in the database
Q2. Which level of expertise is necessary to understand the content explained in the video tutorial on billing statements?
A. Beginner
B. Intermediate
C. Expert
D. Developer
Q3. What is essential to understand before viewing the billing statement tutorial?
A. How to create and use VBA code
B. How to generate aggregate queries
C. The basics of email marketing
D. Advanced database encryption techniques
Q4. What type of query did the presenter mention needs to be used to generate a list with totals of unpaid orders?
A. Append query
B. Make-table query
C. Delete query
D. Aggregate query
Q5. Which Microsoft Office feature allows the addition of the current date into a field?
A. Ctrl + C
B. Ctrl + D
C. Ctrl + ;
D. Ctrl + Shift + ;
Q6. What type of information should not be included in an aggregate query as used in the video?
A. Short text fields
B. Date fields
C. Numeric fields
D. Long text fields
Q7. What was used in the video to sum up all the line item totals for each order?
A. Average function
B. Count function
C. Sum function
D. Min function
Q8. What type of reports did the presenter say functions similar to a form footer regarding totaling values?
A. Group header
B. Report body
C. Group footer
D. Report footer
Q9. Why does the presenter suggest watching his previous videos before trying to create billing statements?
A. To increase his video views
B. To understand the prerequisites like making invoices and using group levels
C. To understand advanced programming concepts
D. To become familiar with his teaching style only
Q10. What reason did the presenter give for not including all fields from a customer table in a query?
A. To comply with data privacy laws
B. To reduce confusion by simplifying the data
C. To increase the processing speed of the query
D. To prevent SQL injection attacks
Answers: 1-B; 2-C; 3-B; 4-D; 5-C; 6-D; 7-C; 8-C; 9-B; 10-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.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on creating monthly billing statements for your customers. In previous lessons, I explained how to generate invoices using Microsoft Access, but today I will show you how to go a step further and provide your customers with a summary statement that lists every outstanding order they need to pay for—typically on a monthly basis.
The need for this came up in a question from Dexter, one of my Platinum members, who asked how to send a summary each month showing every unpaid invoice for a customer. This process might seem complicated at first, but when broken down into individual steps, it becomes manageable even if you are not an advanced developer. There is no VBA code required for this task, but you should be comfortable with working beyond Access basics.
Before jumping in, you'll need to be familiar with concepts from my invoicing tutorials—especially creating invoices themselves. These build the foundation for billing statements. Understanding grouping levels and using them in reports is essential, since this will allow you to pull together all of a customer's invoices in a meaningful way. You also need to know how to build an aggregate query, which is used to summarize line items and create order totals. If you are not comfortable with these topics, I suggest you watch my free tutorials on them, available on my YouTube channel and on my website.
For this demonstration, I am using my TechHelp free template database, which you can also download from my site. In the sample database, each customer can have multiple orders entered. To prepare, I updated some test orders with current dates, marked some as paid, and left others unpaid to mimic a real world scenario. For example, one order from May 1 is marked as paid, while orders from May 2 and May 3 remain unpaid. Only unpaid orders should appear on our billing statement.
Next, we need to prepare a set of queries to build out the data for our billing statement. You might remember from the invoicing lessons that the order detail query is responsible for computing the extended price for each order detail line (essentially multiplying quantity by unit price). We can reuse this query for our purposes.
Moving on, we need a query that lists all of the unpaid orders, along with their calculated totals from the order details. To do this, I start by creating a new query with the orders table and the previous order detail query. These are joined by the Order ID. Initially, the result will show each individual line item for every order, but what we really want is an aggregated view—showing one line per order with the total amount due for each.
To accomplish this, we turn it into an aggregate query. When making aggregate queries, remember not to include the asterisk since you cannot use it in that context. Instead, pick the individual fields you really need, such as Order ID, Customer ID, Order Date, whether the order is paid, and the description. We also include the extended price but set it to sum up all the line item totals, giving us the overall order total.
Once this query is created, we want to see only unpaid orders. You do this by adding criteria to the "Is Paid" field, filtering out any orders that have already been settled. Now we are left with only the unpaid invoices, with neat totals. At this stage, I also like to rename the sum of extended price with an alias like "Order Total" so it looks more presentable and is easier to reference later on.
The next step is to add customer information to this data since a statement for a customer should include their name, address, and other contact details at the top. To do so, create another query on top of the one we just built. Bring in the previously saved query listing unpaid orders, then join it with the customers table to pull in the relevant information—such as first name, last name, and address fields you want to display on the statement. I recommend you do not use the asterisk for multiple tables as it can get messy; just select the specific fields you want.
Once this query is complete, it contains everything required to assemble a billing statement: each unpaid order, the amount owed, and customer details beside it. This makes it much easier to lay out the report.
That brings us to the end of today's lesson. In part two, which will be released on Monday, I will walk you through building the actual billing statement report using the data we just prepared so you can format and send it to your customers. Members will have instant access to the next part, but everyone else can join me next week.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Creating billing statements for customers
Preparing sample orders for statement generation
Updating and managing order payment statuses
Building queries for order details and extended prices
Joining order and order detail queries
Aggregating line item totals for order totals
Filtering unpaid orders using query criteria
Assigning aliases to aggregated fields in queries
Adding customer information to order queries
Saving and naming queries for organization
Combining order and customer data for statements
|
