Accounts Payable 5
By Richard Rost
11 months ago
Build an Accounts Payable Database in Access, Part 5 In this Microsoft Access tutorial I will show you how to build and format an accounts payable report, including how to join payee data to your existing payables query, add grouping and sorting by payee, customize report headers and layouts, and adjust formatting like label placement, column alignment, and background colors. This is part 5. MembersThere is no extended cut, but here is the file 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 CoursesUp Next
Keywords TechHelp Access, accounts payable report, grouping and sorting, payables query, payee ID, payee name, custom report design, report header formatting, group footer totals, record source setup, alternate row color, print preview settings, column alignment, ad hoc join, customer code best practice, sample database download, blank report template, detail section formatting, report labels, columnar report layout, header background color, report totals per client, form filter totals
Intro In this video, we continue the accounts payable series in Microsoft Access by showing how to create and format a payables report using your existing queries and templates. You'll learn how to set the report's record source, add and organize fields from a query, arrange the layout in columns, group records by payee ID, sort by payee name, and customize headers, footers, and formatting options. I will also walk through disabling alternate background colors, editing label appearances, and ensuring a clean, professional look for your Access reports. This is part 5.Transcript Today is part five of my accounts payable series. If you haven't watched parts one through four yet, go watch those. You'll find links down below and then come on back.
Now we get to the part of the database that I always save for last. I always do my reporting last. I like to get the tables, queries, and the forms, all of that done, get everything working. Then, once I'm happy with the workflow, I worry about the reports.
It's weird because I usually use reports as my blueprint for building the database in the first place because I see the output that the client wants. I think, now I'm going to need to build a database to do it this way to collect the information to make that report. So even though I always start with reports as far as for planning the database, they're usually the last thing that I build once all the forms and everything are all done.
We've got our payables query that we built in the last class and we've got each payee ID. The paid date should definitely be null because that's not paid. Then we've got what's the past due, due in 7, due in 30, and over 30. You can change this if you want to, make it less than 30, 30 to 60, 60 to 90, overnight, whatever.
Something we will need in here, I'm going to add in the payee's name. So I'm going to add in that payee query that we created earlier. Now remember payee ID is the same as customer ID. So we'll make that join manually. That's called an ad hoc join because it's not a system relationship and we shouldn't have to worry about this join type because everybody over here should have a matching record over there.
Unless you allow your users to put in items in the bill table without a payee ID. Some of my clients like to do that, they just want to be able to type in things like petty cash or miscellaneous, whatever. To which I say, make that a payee. Anything that you can send money to or that you get money from should be a payee in your system or a customer in your system.
Here we're going to add payee name. If we take a look now, we just get that. This is all we need to create our report. Save that, close it.
In my blank database template, I've got a blank report here. It's just got the margins and everything all set up the way that I like it. So I'm going to copy and paste this guy. Copy, paste. This will be my payables report.
Design view. Now I got this grouping and a group sort and total on. I'm going to just shut that off for just a minute. This is one of the only things I like to work on maximized. It's just easier.
First thing, we're going to open up the report properties. We're going to go to data and we're going to bind this report to that payables query. That's where you get your records from, your data from. The record source.
Now that I've done that, I can go to the report design and add existing fields. There they are. What do we need over here? Let's just bring everything in for now. Click, shift-click, click and drag, drop everybody there.
All right, so there's each record in here. Let's take a peek at what it looks like. Let's save it. I've got print preview right up here on my quick launch toolbar. I'm going to click on that.
There we go. There's each record. Kind of boring. Close that.
What I want is to have that column view, columnar column, however you want to pronounce it, across. Now I am going to get rid of these labels. Actually, I'm going to get rid of all but one label. Delete. I'm going to take this guy and put it above everybody else.
Now I got a report header and a page header, which I'm going to just close these up for now. We don't need these at the moment. But I want to group these based on the payee ID. So we're going to turn a grouping level on.
If you're not familiar with grouping and sorting levels, go watch this video. Let's go back into report design and turn that group and sort back on. I turned it off and then it's fine.
We're going to add a group. We're going to group it on payee ID. You might be thinking to group it on payee name. But I always like to group on the ID and then sort it on the name because if you do happen to have two payee names that are identical, they'll all get grouped together. So try not to group on text like that. Always group on your IDs.
We do want a footer for this group. So come over here and pick with a footer section. There's the payee ID footer.
Then we're going to add a sort after that based on payee name. So inside that group, they'll be sorted by payee name. In fact, now that I think about it, that's backwards. You want to sort by the name, but inside that name, you want to have it grouped on payees. In case you do have two AAA Insurance, you want them grouped separately by the ID, but we want it sorted by name first.
So what we're going to do is we're going to come over here and click on this little up arrow. That'll move the sort above the grouping, and that's fine. So we're going to sort on all the payees first and then inside of that, we're going to group on their ID just in case you get two identical payee names.
Now, take this payee ID and I'm going to cut that label off it and put it in the payee ID header. I'm just going to change that to say payee ID and we're going to make it so we can actually read it. So we're going to go to format and go to black, standard black, and turn off the borders that come around the shapes. In fact, I'm going to do that, now that I'm thinking about it, with all of these guys too. Format, shape outline, transparent. I hate those borders.
Under the payee, we're going to put the payee name in the header. We don't even really need that label. We're going to save it for these guys. Let's put the payee name up here, and I'm going to bold it, control B, and make this a little bit bigger.
Now, do we need the payee ID in here? Not really. Unless, like I said, you've got duplicate payees; then you can display the ID so you know which one's which. In that case, I don't like showing auto numbers. I like making my own type of counter or unique customer ID, or whatever you want. That's not an auto number. I have another whole video on that. I generally don't like showing my auto numbers in my final databases. I use them for training purposes to teach beginners their importance. When it comes down to a finished product, I generally like to hide them. I create my own customer code, and this video explains that in more detail.
So no, we generally don't need the ID in here. Pay dates are always going to be null, so we don't need that. Then we have past due, due in 7, due in 30, and over 30 right there.
I like all of my columns to be left aligned. That's just me. It's a personal preference. If you don't want to do that, don't do it, but I think it looks better.
Now I'm going to take this label and we're just going to use this one label for all of these fields. We just come in here and go: past due, and then a bunch of spaces, due in 7, a bunch of spaces, due in 30, a bunch of spaces, and over 30. You can do whatever formatting you want for this, italicize it, or round up there.
We don't need this much space in the detail section, so we're going to click here and drag that all the way up. Save it. Let's get a good print preview. Where are you? There you are. Looking a lot better.
Now I don't like the alternating colors that you always get with groups. In fact, I turn them off in my template blank for the detail section and the regular headers. But when you create a new header, a new grouping section like that, it gives it back to you. So I'm going to open up the properties for the payee ID header, go to Format, and set the alternate color to no color right there.
I do want the background color for the header itself to be a light gray, so we're going to go maybe there. Then the same thing for the footer, we're just going to change that guy to no color. That's my preference. That's what I like to do.
Save it. Print preview it. That looks so much better. You'll see this here, how that's all white. This text box has a white background. All we have to do is make that transparent. Click on this guy. You can see how you can't see the grid dots behind it. Click on that guy, go to Format, and then go to Shape Fill, and then go transparent.
One more check. Looks beautiful. Looks beautiful. It's coming right along. It's looking pretty good.
How about some totals? We're going to do some totals for each client. We'll do some totals for everybody, so you could say you have this much money total that you have to pay out in 30 days or less, or 7 days or less. Then we'll do a total this way, so you could see the total for each of the customers going across this way too.
We will tackle all of that in my Rows class. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now.
If you're impatient and you don't feel like becoming a member, which you should, go watch this video. It's called Form Filter Totals. It's basically the same thing we're going to be doing. This works in forms. It's basically the same as in reports. I'm going to show you a couple more tricks, but this will get you started.
So there you go. There is your TechHelp video for today. That's part five. Hope you learned something.
Live long and prosper, my friends. I'll see you tomorrow for part six.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free. Make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube is pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. And did I mention it's completely free?
If you like level one, level two is just $1. That's it, and it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now.They also get one free beginner class each month. And yes, those are from my full courses.
Gold members get the previous perks, plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault, where I keep tons of different functions and all kinds of source code that I use. Gold members also get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks, plus they get all of my beginner courses, from every subject, and you get one free advanced or developer class every month after finishing the expert series. You can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by accesslearningzone.com.
I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a payables report in Microsoft Access
Binding a report to a query as the record source
Adding fields from a query to a report
Arranging report fields in a columnar layout
Grouping report records by payee ID
Sorting groups by payee name
Using group footers in Access reports
Customizing report headers and labels
Removing and editing field labels
Formatting column alignment in reports
Adjusting report detail section spacing
Disabling alternate background colors in group headers
Setting header background colors in reports
Making text box backgrounds transparent
Previewing and saving Access reports
COMMERCIAL:
In today's video, we are continuing with part five of the accounts payable series by building reports for our Access database. You will learn how to set up an ad hoc join in your query to include payee names, copy a blank report template, bind your report to the right query, and bring in your fields. We'll discuss how to format your report, set up grouping by payee ID, sort by payee name, and organize your report headers and footers for a cleaner look. You'll also see how to adjust formatting, remove unwanted colors and borders, and align your columns for a better layout. Get ready to make your reports look polished and organized. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why does the instructor usually build reports last when creating a database?
A. Reports are less important than forms and tables
B. Reports are used only for advanced users
C. Reports depend on having working tables, queries, and forms
D. Reports require no queries to be built first
Q2. What is commonly used as a blueprint when planning a new Access database, according to the video?
A. The client's required output reports
B. Table relationships
C. Macros and modules
D. Default database templates
Q3. Why is the join between the payee and bill tables considered ad hoc in this context?
A. It uses a one-to-many relationship
B. It is a permanent system relationship
C. It is created manually without being defined in the relationships window
D. It requires a third table to connect
Q4. What should you do if clients want to enter bills without selecting a payee?
A. Let the bill table accept blank payee IDs
B. Add a payee record for categories like petting cash or miscellaneous
C. Ignore these entries and leave them unlinked
D. Use the customer table instead of payee
Q5. Why does the instructor prefer to group reports by payee ID and sort by payee name rather than group by name?
A. Grouping by name always produces errors
B. Two payee names might be identical, but their IDs are unique
C. Grouping by ID saves more storage space
D. Names cannot be used for sorting in Access
Q6. What is the main reason the instructor does not like to display auto number fields in finished reports?
A. They might be confusing for end users
B. Auto numbers cannot be formatted
C. They are essential for sorting only
D. Auto numbers increase file size
Q7. Which personal formatting preference did the instructor mention for column alignment in reports?
A. Center aligned columns
B. Justify aligned columns
C. Left aligned columns
D. Right aligned columns
Q8. What did the instructor do to the label for the columns in the report?
A. Kept all individual labels for each field
B. Deleted all but one label and made a combined label for multiple fields
C. Turned labels into hyperlinks
D. Moved all labels to the page footer
Q9. How does the instructor handle alternating colors in grouping sections of reports?
A. Leaves the alternating colors as default
B. Changes all group headers and details to have no alternate color
C. Changes alternate color to red
D. Applies conditional formatting instead
Q10. What is added to the report to make it easier to distinguish the payee header visually?
A. Increased the font size only
B. Added a light gray background color to the header
C. Changed font to italic
D. Hid the header section
Q11. For what purpose will the following class (rows class) provide new functionality for the report?
A. Adding additional groups by date
B. Creating totals for each client and for all payables
C. Printing each report page in landscape
D. Exporting the report to Excel automatically
Q12. What is the role of the 'Record Source' property in a report, as described in the video?
A. Specifies the filename of the report on disk
B. Indicates the default printer for the report
C. Defines the query or table that supplies data to the report
D. Determines the security settings of the report
Q13. Why does the instructor advise not to rely on grouping by text fields when building reports?
A. Text fields cannot be sorted in Access
B. Text fields make the report run slower
C. Two identical text entries would result in improper grouping
D. Only number fields can be grouped in reports
Q14. What is the main takeaway about customizing report appearance in Access, based on the instructor's workflow?
A. Appearance can only be changed by editing VBA code
B. Appearance settings are fixed once the report is made
C. Personal preferences such as colors, alignment, and borders can all be changed
D. Default Access templates have the best appearance and should not be changed
Q15. Which of the following is NOT mentioned as a benefit of Access Learning Zone membership?
A. Free beginner classes
B. Access to sample databases
C. Discounts on Microsoft Office licenses
D. Access to the code vault
Answers: 1-C; 2-A; 3-C; 4-B; 5-B; 6-A; 7-C; 8-B; 9-B; 10-B; 11-B; 12-C; 13-C; 14-C; 15-C
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 covers part five of my accounts payable series. If you have not gone through parts one to four yet, I recommend starting with those before proceeding. You will find links to those previous lessons on my website.
At this stage, I always focus on reporting last in the database design process. My preferred order is to first ensure that all tables, queries, and forms are operational and that the workflow is smooth before I shift attention to creating reports. Although I often use reports as a blueprint when I start planning a database, mapping out the output the client desires, the actual construction of reports typically comes once all the other elements are in place and working properly.
We already have the payables query from the previous lesson, listing each payee ID, ensuring the paid date is null for outstanding items, and showing date-based groupings: past due, due in 7 days, due in 30 days, and over 30 days. You are free to modify these groupings as you see fit, for example adjusting them to less than 30, 30 to 60, or different time spans to tailor the report to your needs.
To include the payee's name, I incorporate the payee query we created before. Remember that payee ID in this system is the same as customer ID, so we need to join the tables manually. This is known as an ad hoc join, meaning it is not a permanent relationship in the database's structure but rather a temporary one for this query. Normally, this should not pose issues as every item should have a corresponding payee, unless your users prefer to enter bills without a payee ID for things like petty cash. In that case, I always advise creating a payee for those items, as anything you might pay money to, or receive money from, should be entered as a payee or customer.
Now, after adding the payee name to the query, everything needed to build the report is in place. I save and close the query.
When starting the new report, I use my blank database template, which already includes my preferred margins and layout settings. I make a copy of this blank report for my payables report and begin building from there.
The first step is to bind the report to the payables query by setting its record source property. Then, I add in all relevant fields from the query to the report. Once the fields are in place, I take a look at the print preview to see a basic report layout. Initially, the layout is plain and lists each record separately.
To improve the layout, I want to use a columnar design. I clean up the labels and reposition a single label above the data fields to serve as a header.
For grouping, I add a grouping level based on payee ID. If you are new to grouping and sorting, I recommend watching my complete video on that topic for a deeper understanding. I group on payee ID rather than name, since grouping on text fields can lead to confusion if there are duplicate names. Instead, I sort by payee name within the grouping on ID, ensuring any duplicate payee names are still kept separate according to their unique identifier.
With this grouping in place, I edit labels and headers accordingly. In the header for each grouped payee ID, I display the payee name, make it bold, and increase its size for clarity. Normally, I do not display the payee ID in the final report unless it is necessary to distinguish between duplicate names. In those rare cases, rather than showing an auto-generated ID, I prefer using a custom code or identifier.
The main fields displayed in the report are past due, due in 7, due in 30, and over 30, with the columns left-aligned for a cleaner appearance. I also consolidate the labels into a single header with each category separated by spaces.
After tightening up the detail section to remove unused space, I check the print preview again to review the appearance. I prefer to remove the alternating background colors that Access assigns by default to grouping sections. I do this by setting the alternate color property in the payee ID header and footer to no color. I use a light gray background for the header for subtle emphasis. If any text boxes still show with a white background, I adjust their properties to make them transparent so the grid is visible behind them.
At this point, the report is much improved visually. The next logical step is to add totals - both per client and for all payables, so you have a clear understanding of what is due within different time frames. These totals will show both by payee and for the entire report, giving you summary and detailed information at a glance.
We will explore totals, including row and column totals for each customer, in the next lesson in this series. Stay tuned for part six, when we will cover these features. If you do not want to wait, I have another video titled Form Filter Totals, which outlines similar principles for working with totals, mostly on forms but the approach carries over to reports. That video provides a solid starting point until the next lesson is ready.
If you are new to Access, remember to check out my free Access Level 1 course which covers all the basics from tables, queries, forms, and reports. Level one is available on my website and YouTube channel and is completely free. If you find it helpful, Access Level 2 is just $1 and free for channel members.
If you are seeking additional help with your Microsoft Access project, whether as a tutor, consultant, or developer, you can find expert assistance through my Access Developer Network on my website.
For those interested in more in-depth materials and resources, channel memberships offer many benefits, including access to extended cut TechHelp videos for Silver members, sample database downloads and a code vault for Gold members, and even complete course access for Platinum members, including advanced class offerings after completion of prerequisite lessons.
All relevant links and resources mentioned here are available on my website.
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 payables report in Microsoft Access
Binding a report to a query as the record source
Adding fields from a query to a report
Arranging report fields in a columnar layout
Grouping report records by payee ID
Sorting groups by payee name
Using group footers in Access reports
Customizing report headers and labels
Removing and editing field labels
Formatting column alignment in reports
Adjusting report detail section spacing
Disabling alternate background colors in group headers
Setting header background colors in reports
Making text box backgrounds transparent
Previewing and saving Access reports |
