Billing Statements 3
By Richard Rost
2 years ago
Create Billing Statements for Unpaid Invoices, Part 3 In this Microsoft Access tutorial, I will show you how to create billing statements for unpaid customer invoices, focusing on embellishments, separating customer pages for printing, and summing order totals. We also cover modifying report appearances and utilizing concatenation for customer names. This is the third and final part of my billing statements series. MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, create billing statements, unpaid invoices, custom report layout, query in Access, report grouping Access, customer data totalizer, separate page reports, force new page Access, report customization, footer properties Access, sum function Access, concatenate fields Access, Access print preview, design viewer Access, Access tutorial part 2, Access series, detailed billing report
Intro In this video, we continue working on billing statements in Microsoft Access by making several enhancements to the report layout. We'll talk about disabling alternating colors in group headers and footers, forcing each customer's statement to print on a separate page, and using concatenation to display customer names. You'll also see how to add and configure a sum field to show order totals for each customer, and learn some tips about working between design view and print preview as you make these adjustments. This is part 3.Transcript It's part 3 of my billing statements series. You know what that means. You haven't watched parts 1 & 2 yet. Go watch them. What are you doing here? Get out of here. Go on. Beat it. Come on back when you're done.
Alright, so where do we leave off? Our order unpaid customer thingy. Let's take a look at it. It's getting there. It's getting there. The bulk of the work is done. We just got some little embellishments, some enhancements to add to it. Let's see what it looks like. What are we gonna do next here?
All right, next thing up, let's get rid of this alternating color. Now notice, in my default template, I've got the alternating color turned off for detail sections, but you're still gonna get it in group headers and footers. So that's the next thing we're gonna do. Let's come in here and find the customer ID header. Let's open up its properties, set the alternate background color to no color, or set it the same as this one, whatever you want to do.
I'm not a big fan of it unless we're talking about detail sections without lines and stuff like that. Sometimes they're useful, in this particular case they're not. Same thing for the footer. We're going to use the footer in just a minute. So turn this off as well.
Alright let's see what we got now. Save it and print preview. Okay, looking good.
Now the next thing I can clearly see here is that each customer is on the same page. My goal is to print these out and to mail them to each customer. So I need each customer on a separate page. To do that, we are going to force a new page after the customer ID footer. So after we get the footer information, we're going to force a new page. We've got a whole video on this if you want to learn more about it. Open up the customer ID footer's properties, find force new page. We're going to set this to after section. So when this thing is done drawing, you're going to get a brand new page. And yes, it will include whatever page footer you have.
All right, so now let's see what we've got. Save it. And a lot of my videos when I'm working with forms and stuff, you always hear me say save it, close it, reopen it. That's the case when I'm dealing with coding, for those of you who are my developer students. There's no coding in this, so there's really no need to close it and reopen it again. Once I start putting code in here, which I'm not doing with this one because this is a non-programming lesson right, that I thought getting in the habit of closing it and reopening it so any code that was supposed to run when it opens will run it doesn't always run when you switch from design view back to regular view it's supposed to but it doesn't. All right, so in here we're fine to switch back and forth from design to print preview.
Alright so there we go, customer one, there's me. Let's go to page two. Oh there's the next one, there's Jordy, and then the next one is Mister Data. Real quick, if you want to concatenate some of these things that's fine too and I cover this a lot of the problems of the first in the last thing together which will also do with like city state is that the label wizard does this for you sometimes, right. You can delete last name, make first name longer like this, right. Open it up. Now the name is no longer in the first name is going to be what's called customer name call whatever you want.
And for the control source, we're going to make it, I'm going to zoom in, we're going to make it equals first name and a space and the last name. That's a little concatenation there. And again, if you're not familiar with concatenation, I got a whole video on it, I'll put a link down below. But now, if we go to print preview, you can see it's nice and together and it's no longer Geordie space LaForge, it's big space. I have a week total up with a or so they can just write a swan check the covers of the rest of the total down here. Now this is why I told you before less than one to watch my video on form for a total of three work the same here right, so in the customer ID be funny when I put a toll for each customer right total all the detail items for each customer we're gonna put right here below the order totals copy this click down here, paste.
Okay, slide this guy over under here. Okay, we're going to open it up, properties, right here, we're going to call this the sum order total, one word, sum order total. Now the control source is going to be, I'm going to zoom in for you, the control source is going to be equals the sum function on all of the order total fields for this customer. Okay, and I just give it the name sum order total so it's a meaningful name. Okay save it let's close this and let's print prev[]
TOPICS:
Modifying Group Header and Footer Properties
Disabling alternating color in detail sections
Forcing new pages after footer sections
Concatenating fields for display purposes
Summing order totals for individual customers
Switching between design view and print previewQuiz Q1. What is the purpose of disabling the alternating color in the customer ID header and footer?
A. To make the report print faster
B. To improve the readability of detail sections with lines
C. Because it is not useful in the specific case of the tutorial
D. To highlight the customer information more clearly
Q2. Why does the presenter want to ensure each customer's details start on a new page?
A. To mail each customer their individual billing statement
B. To save paper when printing
C. To make the billing statements look more compact
D. To comply with legal documentation requirements
Q3. How is a new page forced after the customer ID footer within the billing statement?
A. By setting the "force new page" property to "after section"
B. By inserting a page break manually in each section
C. By increasing the margin size at the bottom of the footer
D. By using a special script to automate page breaks
Q4. What is the purpose of concatenating the first name and last name in the tutorial?
A. To create a more formal appearance in the statement
B. To save space and simplify the customer's name display
C. To enhance the sorting of names alphabetically
D. To comply with new formatting requirements
Q5. What does the control source "=Sum(OrderTotal)" represent in the context of the tutorial?
A. It calculates the total number of orders per customer
B. It displays the sum of all orders' total amounts for each customer
C. It translates the total amount into a different currency
D. It represents a fixed sum for budgeting purposes
Answers: 1-C; 2-A; 3-A; 4-B; 5-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 continues my series on creating billing statements in Microsoft Access. If you have not yet watched parts one and two, I recommend going through those first. This part picks up where the earlier lessons left off, so having that foundation will help everything make sense.
So, where did we leave our project? We already have the basic structure for displaying unpaid orders by customer. Most of the heavy lifting is complete, and now we're going to focus on some refinements and enhancements to improve the final output.
The first tweak involves the alternating background color in group headers and footers. In my standard report template, alternating color is turned off for detail sections, but Microsoft Access leaves it on by default for group headers and footers. For this project, I do not find it helpful to use alternating colors outside of certain types of detail sections, so we are going to disable it for both the group header and the group footer. Open the properties for the customer ID header, and set the alternate background color to either no color or to match the primary background. Repeat this step for the footer as well. Since we will be using the footer shortly, it is important that the alternating color not interfere with our design.
Once you've saved these changes, use print preview to check the result. You will notice a much cleaner look that is easier to follow.
Next, I noticed that all customers are being printed on the same page, but we want each customer's statement on a separate page so they can easily be mailed or distributed. To accomplish this, Access gives you the ability to force a new page after specific sections. In this case, after we finish displaying the information for one customer, we want Access to start a new page for the next. To set this up, go to the properties of the customer ID footer, find the option for forcing a new page, and set it to occur after the section. This will ensure each customer's statement begins at the top of a new page and will include any page footers as needed.
After saving these changes, try print preview again. Now, each customer's statement prints neatly on its own page. If you are used to watching my videos on working with forms, you will remember that I usually say to save, close, and reopen objects—especially when there is code involved, since Access sometimes fails to re-run certain events unless the screen is closed and opened. In this case, since this is purely design work without VBA, there is no need to close and reopen the report each time. You can safely switch between design view and print preview without any issues.
Next, let's improve how customer names are displayed. If you want to display the first name and last name together, you can use a technique called concatenation to create a single field displaying both. Instead of using separate text boxes for each name, which often leaves awkward gaps and is less readable, it is a good practice to combine them into one field for neatness. Simply delete the last name control, extend the first name box, and rename it something clearer for your purpose, like "customer name". For the control source, use an expression that combines the first name, a space, and the last name. This makes the display more professional and is easier to work with if you need to refer to the full customer name elsewhere. If you want additional guidance on concatenation, I have a separate video dedicated to that topic on my website.
Now let's move on to one more important enhancement: calculating the total of all orders for each customer directly in the report. This is helpful when customers want to see a summary amount and makes it easier for them to pay. To do this, add a new text box in the customer's group footer, underneath the list of orders. Copy the control that shows the order total, paste it in the footer, and move it to where you want the group total to appear. Name this control something descriptive like "sum order total". For the control source, set it to the sum of the order total fields for that group. This way, each customer's statement will conclude with a summary of all their unpaid orders.
After making these changes, review your work in print preview to make sure everything looks right. Each customer now appears on their own page, each page has the totals clearly marked, and the names are combined properly.
In this lesson, we covered several useful topics: how to modify group header and footer properties, turn off alternating colors, force new pages after footer sections, concatenate fields for improved display, sum order totals for each customer, and work efficiently between design and print preview modes without unnecessary reopening of the report.
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 Modifying group header properties
Disabling alternating color in group headers
Disabling alternating color in group footers
Forcing a new page after group footer
Concatenating first and last name fields
Renaming label and control to customer name
Setting control source to concatenated name
Summing order totals in group footer
Setting control source for sum of order totals
Switching between design view and print preview |
