Billing Statements 2
By Richard Rost
2 years ago
Create Billing Statements for Unpaid Invoices, Part 2 In this Microsoft Access tutorial, I will show you how to create detailed billing statements for unpaid customer invoices. We'll design a report using queries and groupings, customize the layout and fields, and ensure each customer's data is clearly displayed and totaled on separate pages. This is part 2 of our 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, create billing statements, unpaid customer invoices, expert-level Access tutorial, Access VBA-free solutions, grouping in reports, order unpaid customer query, record source setup, report and form linking, group and sort feature, report property sheet, Access form design, adding fields to report, handling unpaid orders, report design, Access user interface tips, aggregate queries, grouping levels
Intro In this video, we continue building billing statements in Microsoft Access by creating a report based on a query of unpaid customer orders. I will show you how to use blank report templates, rename reports to match their data sources, bind your report to the needed query, and add fields from your queries to the report layout. We will cover grouping orders by customer, adding headers and footers for summary information, arranging and customizing fields, managing formatting details, and using print preview to check your work along the way. This is part 2.Transcript Today is part two of my billing statements series. If you have not yet watched part one, go watch that first. You'll find a link down below in the description and then when you're done with that, come on back. Alright, in part one we got together all the queries that we're going to need to put together our report. We've got our order unpaid customer query that's got all the order information from our unpaid invoices along with the customer details. Now we can start putting together our report.
If you watched my blank template video, you know that I like to have some blank forms in here and some blank reports so I can use those as templates for making new ones. So, I'm going to take a copy of my blank R, copy paste, ctrl C, ctrl V. We're going to call this my order unpaid, unpaid customer R. It's going to be based on that query so that's what I like to name the report. You can put, when you put a button for it on your main menu, call it whatever you want, billing statements, I don't care. But as far as naming it internally, that's what I like to do. I like to make the report and the form or whatever as closely named to their record source as possible.
Alright, so let's open this guy up for design view. Now I got this group level, so we're going to turn this off for just a minute. We're going to come back to it. Don't worry about it. Alright. Here's what my blank looks like. I'm going to get rid of you for now. And the first thing we're going to do is we're going to bind this report to that query that we just created. So go over here, double-click where the rulers come together. And by the way, if you don't have this, if you don't see the ruler bars, this comes up once in a while in the forums. Just right-click and go to Ruler. Notice I just turned them off. It comes up in the forums a lot. Like people say, 'Well, I don't have the ruler bars. I don't have that little box there where I can double-click to bring up this property sheet.' Well, right-click on the report and go to Ruler. And that'll turn it back on. See, and then you got this little nifty guy there. You can double-click on that to bring up the property sheet. But in here, let's go to the All tab or the Data tab. Either one. Find Record Source. This is where this report is getting its data from. And that's going to be the query that we just made in the last video. So, that guy. Now that we have that set, we can close this and we can go to our report design tab, find add existing fields and just bring everybody in. So, I'm going to click on order ID, hold the shift key down, click on the last item and then let the shift key go, click and drag and drop everybody right there. Okay, just bring everybody over. Now we don't really need is paid because these are all unpaid, so we don't need that field. The customer stuff I want to go up top but I don't want to go on the page header, I want to have a group for the customer stuff to go in so we're going to add a grouping level. Alright, someone's beaming in. So up here on the report design go to group and sort, that's going to turn this thing on that I turned off a minute ago. I had it on because I was playing with it earlier but I don't normally leave this on. Okay, we're going to go add a group. What do we want to group by? We want to group all of what together? Well, the orders are going to be the line items for this right and they're going to be all grouped together by customer so we need a customer group. Each customer is going to get their own reporter, their own page of stuff showing all of the orders that are unpaid. Okay, now I do want more stuff to put on more right, fire values fine, that there is an option to have a patrols on the forty but I like doing that manually, I don't like using their so we're going to skip that for now.
Alright, we're going to come over here. It says with a header section. Yeah, I want the header section. And then without a footer section, I'm going to drop that down and go with a footer section. I want a customer footer, so I can put their order total in that. Okay? Alright, so I've got a customer ID header and a customer ID_footer. So I'm done with this thing now, so we're going to close this tab. Now, don't click that. That deletes the group - one of the things, one of my annoyances - access, this and this are too similar. Sammy put that on a list or even a list of all our Access suggestions for the Microsoft team, this - and that's why I see a lot of beginners get confused by these - this deletes the group, this just closes the pane. I'll close the pane for now and the group still exists. Let's take all of the customer stuff and put it up here. In the customer, has a little bigger, first order can stay here, customer ID can go up here, the order dates can get a go next to that, the description of the over here, or it was in the over here - alright, first name, last name, and address - these are all customer-related fields for those are going to appear. I think I'm doing and all the order stuff here. These are my details and they're all going to be grouped together under the customer. Okay? Alright, let's take a second and just pretty this up a little bit. We're going to put customer ID like that. That's a label. I can put a space there. Okay? We got the first name and last name but I'm going to make this just say name like that and we'll get rid of the last name. We'll just put these next to each other like that. Okay? And then we got an address can go here. Next week okay they got an address can go here. You're going to have the address, city, state, zip, country - a whole, you know, a whole big address block. Again, for class this is an I just you can see what's going on you can make yours pretty later I got many many videos on making stuff pretty they were just gonna get this bill okay so there's a customer stuff now the order stuff on a lot of little differently I would take the I'm going to use this little box here and click and drag. See, I'm doing that. I cover this stuff in my beginner classes. So if you don't know all this stuff about moving controls around, go watch Access Beginner 1. I take a lot of time to teach this stuff. Alright, the description can be longer like that. We got our order total. We'll put the order total at the end. Okey dokey. I just missed. Does it line up? That's close enough. Okay, maybe order total. Like I said, I'm going to put the order total at the end. I'm going to put the order total at the end. I'm going to put the order total at the end. I'm going to put the order total at the end. dismissed that wind up as close came in the order total with that okay bring that up all right let's save it control s and let's take a peek see we got the first you look what it's looking like a right click here and go to print preview okay alright it's going along with shading is where the but I was going on here I think it's customer is customer one I've got the two orders for customer one that are they're not pay okay and then there's a stupid it with his order okay all right it's getting there along first thing I want to do is notice that the header right here the labels are appearing for each order let's let's get rid of that we're at a welcome back in here designed you these labels need to go up here at the top of all the orders so the bottom of the customer had a little more room here like that with the staff and I'm right I'm going to select all of these come out and collect Like that, just move this guy up and down. I'm going to select all of these, cut them out, Ctrl X, click up here and hit paste. Alright, now just slide these down where you want them, right about there. Okay, maybe even, how about we put a horizontal line in here. Come up to your toolbox, find the horizontal line tool, and then we'll just draw a nice little horizontal line right there. Okay, alright, bring this up just a bit, bring these guys up underneath them like that, leave as much space as you feel you need. Alright, save it. Let's take a peek. At this stage, it's a lot of do a little bit, and then see what it looks like. Do a little bit, and then see what it looks like. There's a layout view but I really hate the layout view. I just don't like layout view. I like to just switch between design and print preview. I've had lots of problems with layout view in the past. When it first came out, it was really buggy and it just kind of left a bad taste in my mouth. Okay, looks good, looks good, we're getting there. Let's get rid of all the boxes that are around the fields. That's kind of like a default setting. And let's left-align our numbers. Because all the numbers come in right-aligned. I know some of you like dollar amounts to the right. I don't personally, but it's up to you. They're hey, they're your Legos, you can put them together however you want. So, I'm going to select all of these. And we're going to go to format and go to shape outline transparent and then we're going to do the same thing with these. Shape outline transparent. And then all the number values. So customer ID, order ID, order date. Yes, you can select multiple ones but it doesn't always work. It's another one of my pet peeves. Alright, let's see what we got. Okay looking better, looking better. We're getting there, we're getting there. Alright, it's coming along, we still got some things to do, we still gotta break this up so each customer is on their own page, we gotta get rid of this background shading, we gotta add order totals so you can total this up at the bottom here, make this look like currency. Lots of little stuff to do, maybe put a statement header across the top with your address. We're gonna cover a lot of that in you guessed it, tomorrow's video part three, got lots more to do. So tune in tomorrow, same TechHelp time, same TechHelp channel or of course if you're a member you can watch it right now because I'm going to record it in just a few minutes. But that is going to be your TechHelp video for today. I hope you learned something my friends. Live long and prosper. I'll see you tomorrow for part 3.
TOPICS:
Creating Access reports from queries
Using Access templates for reports
Renaming reports based on data sources
Accessing design view in Access
Binding reports to queries
Adding fields to Access reports
Configuring report properties in Access
Adding groupings in Access reports
Designing grouped reports by customer
Configuring report headers and footers
Adjusting field placements in reports
Customizing report layouts
Removing default field borders in Access
Aligning text in report fields
Configuring print preview settings
Troubleshooting layout issues in AccessQuiz Q1. What is the primary purpose of the video tutorial series discussed?
A. To teach how to design a video game interface
B. To demonstrate advanced photography skills
C. To guide viewers through creating billing statements
D. To explain stock market fundamentals
Q2. What resource does the narrator suggest viewers consult if they haven’t watched the first part of the series?
A. An online course
B. The help section in the software
C. A link in the video description
D. A textbook on billing systems
Q3. What does the narrator suggest naming the new report created during the tutorial?
A. Billing Statements
B. Order Unpaid Customer R
C. Financial Overview
D. Purchase Summary
Q4. Which Windows shortcut keys are mentioned for copying and pasting?
A. Shift + C and Shift + V
B. Ctrl + C and Ctrl + V
C. Alt + C and Alt + V
D. Cmd + C and Cmd + V
Q5. What functionality does grouping provide in the report being created?
A. It sums up all orders regardless of the customer
B. It separates the report by different types of payments
C. It arranges orders under each customer
D. It filters out paid orders automatically
Q6. When editing the report, what change does the narrator make to the presentation of 'First Name' and 'Last Name'?
A. Combines them into a single 'Name' field
B. Deletes both fields
C. Moves them to the report footer
D. Changes the font size only
Q7. Which is NOT a modification made to the appearance of the report during the tutorial?
A. Removal of default field boxes
B. Left-aligning number fields
C. Adding a company logo to each page
D. Inserting a horizontal line separator
Q8. What action does the narrator take to avoid deleting the group in Access unintentionally?
A. He modifies the software’s settings
B. He writes an email to Microsoft
C. He closes the grouping pane instead of deleting it
D. He locks the group feature
Q9. At which stage does the narrator suggest using a 'Print Preview' to check the layout?
A. After every single change
B. Only after completing all formatting
C. After making a significant amount of changes
D. Never; he recommends using another feature
Q10. What does the narrator plan to cover in the next video?
A. Reverting all changes made in the current tutorial
B. Creating order totals and formatting them
C. Removing the grouping feature entirely
D. Integrating with another software for advanced features
Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-A; 7-C; 8-C; 9-C; 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 continues with part two of my billing statements series. If you have not yet reviewed part one, I recommend watching that first to ensure everything makes sense. In the previous lesson, we built the necessary queries for our report, specifically an order unpaid customer query. This query includes all relevant information about unpaid orders as well as the associated customer details. With that foundation in place, we can now begin assembling our report.
For those who have seen my lesson on blank templates, you already know I prefer to keep some empty forms and reports in my database to use as templates for creating new objects. I start by making a copy of my blank report, renaming it to match the data source it will be based on. In this case, I'll call this report order unpaid customer R, keeping the internal naming conventions in sync with the records they pull from. Public-facing names like "billing statements" can be set on the main menu for clarity, but behind the scenes, I stay consistent for my own organizational purposes.
Next, I open the report in design view. At this stage, Access might show an existing group level from my earlier experiments. I like to disable this temporarily so we can start fresh. As always, if you find that you do not see the rulers in your design view—a common question—just right-click on the report and select Ruler to bring them back. This also gives you the small selector box in the corner, which you can double-click to open the property sheet. Either in the All tab or the Data tab, you will find the Record Source property, which determines where the report gets its data. Here, I set it to the query we previously built.
Once the report is bound to the correct query, I use the Add Existing Fields pane to bring in the relevant data fields. I select everything I need, drag them into the design area, and then arrange them to my liking. Since these are all unpaid orders, there is no need to display an "is paid" field, so I remove that. The customer details belong at the top, but rather than placing them in the page header, I want to group the report by customer, which requires adding a grouping level.
To create the grouping, I turn grouping and sorting on. Then, I add a new group based on customer ID. This way, each customer gets a separate section in the report with their own associated orders listed below. I include a header section for the customer's information and a footer section where I can eventually display totals per customer.
While grouping and sorting, it is important to be aware of Access's interface. There are two icons that appear very similar—one closes the grouping pane and the other deletes the group. It is easy to accidentally remove the whole group when all you wanted to do was hide the pane, so I always make sure to close rather than delete unless intentional.
With the grouping in place, I move all relevant customer fields—such as customer ID, first name, last name, and address—into the newly created customer header section. The remaining order-specific fields, like order ID and order total, stay in the detail section. I then adjust the layout, giving each label a suitable position and spacing out address fields for clarity. Over time, you can further polish the design, but my focus right now is to get the structure set. I cover many neat design tricks in other videos, so here I am keeping it simple and functional for the demonstration.
For moving and aligning controls, I use the selection box tool and basic drag-and-drop techniques, concepts I explain thoroughly in my beginner classes. For example, I increase the width of the description field and place the order total at the end of each line. Once everything is laid out, I save my progress and take a look at the report in print preview mode to see how things are shaping up.
One refinement I make is to move all the column labels up into the customer header, so they appear above each list of orders rather than next to every order. For extra clarity, I use the horizontal line tool to visually separate the labels from the rest of the data. Small adjustments help to give the report a more professional appearance. I continue going back and forth between design view and print preview, because I personally dislike relying on layout view due to issues I have had with it in the past.
To further enhance readability, I remove the default borders around all the fields and left-align the numeric fields, though alignment is a matter of personal preference. Some people like monetary values to be right-aligned, but in my reports, I keep things left-aligned for a cleaner look.
At this stage, the report is coming together, but some steps remain. For complete polish, each customer needs to appear on a separate page, any unwanted background shading needs to be eliminated, order totals should be calculated and displayed in the footer, and currency formats need to be applied. You might also want to add a statement header with your own business information. All these finishing touches will be covered in the next lesson, so be sure to return for part three.
If you are a member, you can watch the next part right away, otherwise, stay tuned for tomorrow when we will wrap up the billing statement series. 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 a report from the unpaid customer query
Copying and renaming a blank report template
Binding a report to a specific query
Accessing and using the report property sheet
Adding existing fields to an Access report
Grouping report records by customer
Adding customer header and footer sections
Moving customer fields to group headers
Arranging order detail fields in the report
Customizing field labels and layout
Combining first and last name fields
Building an address block in the report
Reordering and resizing fields in design view
Cutting and pasting labels between sections
Inserting a horizontal line in a report section
Removing borders from fields
Aligning numeric fields to the left
Switching between print preview and design view |
