Account Statements 2
By Richard Rost
2 years ago
Create Account Statements w Credit & Debit, Part 2
In this Microsoft Access tutorial, I will show you how to create account statements for a check register by differentiating credits and debits. We'll refine the report design, incorporate dynamic totals for each category, and add date criteria via a form for targeted data printing. This is part 2.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, create account statements, separate credit and debit, Access check register tutorial, report design Access, subtotal by section, modify report footer, design view tips, calculate sum of credits, calculate sum of debits, format report elements, report criteria setup, date criteria in reports, access database tutorial, report customization, using expressions in Access, filter report by dates, setup report menu, Access form design
Intro
In this video, we continue building account statements in Microsoft Access by refining report design, simplifying elements, and implementing section-specific totals. I'll show you how to calculate net amounts by subtracting sums, adjust layouts for better clarity, and add dynamic titles and labels. We will also set up date criteria for filtering statement data, demonstrate how to link a menu form to the report for easier interaction, and create command buttons to open related forms. By the end, your account statement report will be cleaner, more accurate, and ready for dynamic date selection. This is part 2.
Transcript
Today is part 2 of my account statements series. If you haven't watched part 1 yet, go watch that first. You'll find a link down below. Then come on back. You're holding up class. Go on. Get out of here. Go watch it.
Alright, so yesterday we got a lot done. We built the report up to this point here. I think after looking at it, I'm also going to get rid of that line that I put in there. I don't like the way it looks. But first off, we also have to fix our totals down here. Now this right now is a total for everything. I want a total for each of these sections.
So let's go back into here, design view, and yeah, I'm going to go to that one. I don't like it. I forgot that these guys have borders around them, so I like the way it looks like that.
Alright, so we're going to take this stuff out of the report footer. Actually, I'm just going to copy it. So let's copy all of this. And, oh, you know what? We don't need separate debit and credit in here, do we? No, we do not, let's see. Yeah, we don't need debit and credit, just amount. Just amount, so design again. I don't know why I didn't do this last time, but there we go, get rid of you, get rid of you, get rid of you. We'll just expand the description here out to there. There we go.
Okay, now I do want this amount up in here in the title first, so I get a total for each section. So I'm going to copy that with its line, copy, click in here, paste. We're going to slide you over there like that. And is it just a bit too far? Let's bring it. Yeah, we'll bring you over here. Sometimes you don't see these until you look at them closely. You can leave a little bit of extra space in here if you want to. Maybe bold this one, make it darker so format shape outline thickness little bit thicker cc right save it let's take a look at what we got, okay, looking good twelve hundred there fifteen seventy there seems about right, now this isn't correct so what we have to do is we have to take the sum of the credits and subtract the sum of the debits. I don't want to add all these things up. So let's modify this guy, go to data, it's not going to be sum of amount, it's going to be sum of credit, let me zoom in so you can see it, shift F2, sum of the credit minus the sum of the debit.
Okay, and yes, you don't need to put these little brackets in here because you don't have any spaces in your field names, like good little programmers. In fact, I usually remove these but Access puts them back in there for you anyway.
Makes it easier for beginners to understand. Okay, alright, save it, close it. I'm going to make this a little bit smaller. Okay, ready? Let's take a peek. Right click, print preview, and looks good. That looks correct. Looks like we had more money going out this month than coming in. Maybe some titles in here. Sum of debits, sum of credits, and then total. That'd be nice. So I'm going to put this in a text box. I'm going to grab any one of these. Grab description, that's fine. Copy, paste, and we'll put it right here, and maybe about that big.
Open her up. Now we don't want this guy bound to description anymore. So we're going to come in here, and the control source is going to be equals, let me zoom in again, shift F2, equals sum of and title. Title is a field that either says debits or credits. That's perfect. Looks good. Then we'll put in here, let's put in here title, whatever you want to call it. And then down below here we'll just put total. You could use a label for that. You don't have to make a text box for that one. That's just a total. Slide you down here. So change that to total. If you want to right-align these, that's fine. Whatever you want to do. There you go. Maybe make this guy bold. And see, these aren't exactly the right size. It's one of my pet peeves. They don't automatically come in on the grid. Let's see. Right click, size to grid. There we go.
Let me do these ones. While we're at it. Right click, size to grid. I know it's off your screen. That's a beginner thing. You should know that. Save it, close it, right click, print preview. OK. Whoopsie. Looking pretty good.
Yeah, the stupid borders, I know. Alright, right click, design view, view, format, outline, transparent, you and you outline transparent. Okay, what else do we need? Well, next we need a way to add some date criteria to this. Okay, because right now I'm just seeing all the records of my form. In fact, let me update the dates in here. It's currently May eighth of twenty twenty-four, so let me back this up to let's go 4-20 on that one. I'm just going to make these a little more current. Let's go 4-23.
Actually, let's back this up to March, so 3-25, because we want it to be before our valid date range. Let's make you 4-25. I'll go 4-26. And then we'll just change this one so they're a little bit out of date, so 5-2. If we do a monthly, I want to see them. And then a 5-4.
Okay. So if we want a last month statement, we should just see these ones, right, from April. Okay. Now the easiest way to do this with a report like this is to put your criteria on a form. Okay? You could put it on this form and then hit a button to open it, but I think that sometimes gets confusing because you'll have different criteria down here, you'll still see all of them up here, you could change the filters, I mean it just gets crazy. So this is where a menu comes in, very, very handy. You could put it on a main menu, you could make a report menu, somewhere else where you have the criteria, and then when you click the button, the form provides the criteria. If you don't know how to do this, go watch this video; it shows how to get a value from an open form.
Alright, here I have a little form. It's got just a state in it. You click the button and it provides the criteria to this query. Okay, go watch this video. Find the link down below. Now I just happen to have a nice little form main menu already set up in my TechHelp free template. You guys I'm sure have seen this one. Come on, open up. Come on. There it is. This guy. I'm just going to grab the main menu out of here, click and drag and drop it in this one. Okay, that's it. Okay, you're done. Bye.
Here's the main menu. Alright, it's got buttons open to custom form, which these things don't exist. So let's just go in here, design view. I'm going to close you for now. Okay, we don't need these buttons because those things don't exist. I'm not going to use this status box. We'll make that nice and small. But what I do need are some buttons to open up the check register form and then some criteria for my statement. Okay? So let's, you know what, let's delete this. I promised I was going to do this without any programming because normally you just throw a line of VBA code in there. But let's create a button, form design, command button, drop you here. Alright, we're going to go form operations, open a form, we're going to open the check register form, we're going to show all the records, and then for text we'll put in here, check register form. OK, check reg form button, and there you go. There's my open the check register form, just to make sure that it works. There you go.
Now, we need criteria for our statement. So we already have one little box here with a date in it. This is just a text box. Alright, and we'll put over here, let's say this will be the start date. Now I'm going to open you up. I don't want today's date in here. You can start with today's date if you want. Let's name this, by the way, let's name this start date. Now instead of it being today's date, let's say by default you are always printing out last month? Wouldn't that be cool? Well, how do you do that? Well, I just happen to have a video. It's called First Day of the Month. How do you find the first day of the month, the last day of the month, the first day of next month, the last day of next month, the first day of the previous month, and all those different things are on this page. Isn't that cool? It makes sense to watch all my videos, right Sammy? Sammy's one of my moderators and he's on a quest to watch all of my TechHelp videos. I think he's up to like what 2022 now Sammy? Anyways, if you go to the page for this class, look at this. Oh here we go right down here, Usage. All right, first day of the previous month is right there. So we're gonna copy this. The only change you have to make to it is if you want the actual previous month, you got to replace D with the date function. That's it. All right, so we're going to copy that. We're going to come up here and in this control source, I'm going to shift F2, zoom in. All right, I'm going to say this is equal to that function, but all we've got to do is replace D, which is a date, with the date function. Alright, and what that will do, is it will put today's date in there. So now, our start date, if I close this and reopen it, will default to the first day of last month. Like I said, it's currently May 8th, so that's the first day of last month. Isn't that cool?
Alright, let's do the end date. Copy paste. Oh, get over there. Okay, end date. And we'll call you end date. And what's the function? Let's take a look at the page. It's going to be the last day of the previous month which is right there. And yes, I have a whole separate video on date serial if you want to learn more about how this thing works. I'll put a link down below. So just copy that formula. We're going to come into here. I'm going to shift F2 to zoom in. We're going to replace that with this. Replace these with today's date. Like so. Hit OK. We're going to save it, close it, open it, and there we go. There's our criteria. Now we're cooking with gas. We're getting there, folks. We're getting there. Now we just have to feed these dates into our report when we open it up, and we'll cover that in tomorrow's video. So tune in tomorrow for part three. Same bat time, same bat channel. Or of course, if you remember, you can watch it right now because I'm about to record it in about two minutes.
In the meantime, if you want to learn more about how that date serial function works, go watch this. And if you do a lot of work with dates and times, I have several lessons in my expert series where I cover all the different date time functions and I put them all together in one seminar called the Date Time Seminar. All right, it covers everything you ever wanted to know about working with dates in Access: workdays and holidays and all kinds of time date calculations, you name it. That's covered in this Date Time Summary. Lots and lots and lots about dates and times and reminders and pop-ups and units, how to figure out holidays, and it's all in here. It's like ragu, it's in there. Or is that prego? One of those spaghetti sauces, I don't remember. All right, but that's going to do it for part two. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 3.
TOPICS:
Report design modifications in Microsoft Access
Implementing section-specific totals in reports
Adjusting report layouts for clarity and aesthetics
Simplification of report elements
Transposing items among report sections
Calculating net amounts by subtracting sums
Dynamically displaying titles based on data conditions
Adding functional text and labels in reports
Setting transparent outlines for report items
Incorporating date filters for statement reports
Techniques for linking forms to reports as criteria providers
Defaulting date selections to specific ranges using expressions
Navigation and menu setup in Access for report interaction
Creating command buttons for form operations
Quiz
Q1. What was the primary goal addressed in the video regarding the account statements?
A. To create a new account from scratch
B. To add a new transaction line
C. To modify the report to show section totals rather than a grand total
D. To change the color scheme of the report
Q2. What did the presenter decide to remove from the report?
A. The transaction details
B. The debit and credit columns
C. A line he previously inserted
D. The entire footer
Q3. What key change did the presenter implement in the account statement report concerning transaction types?
A. Separate debit and credit totals for each section
B. Incorporated both debit and credit in a single column
C. Removed debit and credit, left only the amount column
D. Swapped positions of debit and credit columns
Q4. In the video, what mathematical operation did the presenter use to correct the total amount calculation?
A. Added sum of credits to sum of debits
B. Divided sum of credits by sum of debits
C. Subtracted sum of debits from sum of credits
D. Multiplied sum of credits by sum of debits
Q5. How did the presenter recommend handling criteria for report generation?
A. By entering it directly into the query in SQL view
B. By setting the criteria on a linked form
C. Through hard-coding the values in the VBA module
D. By asking users to type it each time in a pop-up window
Q6. What default value did the presenter set for the start date input on his criteria form?
A. Today's date
B. The last day of the current month
C. The first day of the last month
D. The first day of the next month
Q7. How did the presenter suggest feeding date criteria into the report?
A. By manual entry each time the report is run
B. Through dynamic updates as the database updates
C. By capturing it from a user-input form
D. By automatically reading the system clock of the computer
Q8. What did the presenter use to adjust the layout elements of the report to grid alignment?
A. Dragging manually to fit
B. A VBA script to align elements
C. The 'size to grid' right-click option
D. Automatic alignment settings in the design view
Q9. According to the presenter, what seminar covers extensive use and manipulation of date and time in Access?
A. The Advanced Date Time Seminar
B. The Date Serial Functionality Seminar
C. The TechHelp Date Seminar
D. The Date Time Seminar
Answers: 1-C; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-D
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 part 2 of my Microsoft Access Account Statements series. Before getting started, make sure you have watched part 1 so that everything discussed here makes sense.
In the previous lesson, we set up the core of our account statement report, but there are some improvements we need to address. Looking over the report, I've decided to remove the decorative line I added previously since I am not happy with how it appears visually. Additionally, the totals section currently sums all records, but what I really want is separate totals for each section of the report. This requires moving some items out of the report footer and setting them up properly within each group section.
When making these changes, I also noticed that the fields involved already have borders, which provides the clean look I prefer, so additional lines are unnecessary. Once I had copied and rearranged the needed elements, I concluded that we do not need separate debit and credit fields—just a single amount field is sufficient. So I removed the redundant controls and expanded the description field to use that free space.
Next, I wanted the amount value to appear in the group header for clarity, providing a subtotal for each section. After copying and pasting the control there and carefully aligning it, I made sure it was visually distinct by making the outline slightly thicker and bolding it. This way, the subtotals stand out and are easy to read. When reviewing the totals, however, it became clear that simply summing all the values would not provide an accurate net amount. Instead, we need to subtract the sum of debits from the sum of credits for each section to calculate the correct net value. I updated the control's source accordingly, noting that if your field names do not contain spaces, you do not need to enclose them in brackets. Even though I like to remove those brackets, Access will sometimes reinsert them automatically, which is helpful for beginners.
After saving and previewing the report, everything looked good in terms of calculations, showing the proper balance for each section. At this point, I thought it would be nice to enhance the layout further by including summary labels such as "Sum of Debits," "Sum of Credits," and "Total." To do this, I added a text box for the title near the totals area and set its control source so that the appropriate label is displayed dynamically based on the data. Additionally, I inserted a simple label for the total row. Adjusting alignment and formatting ensures everything looks polished and professional. One tip I like to share is to use the "size to grid" option to keep controls neatly aligned, since Access does not always snap them perfectly into place.
Once these formatting tweaks were complete, I checked the print preview again. At this point, I decided that some elements could benefit from transparent outlines for a cleaner appearance. Setting the outlines of those controls to transparent made the report visually clearer.
With the basic layout and calculations in place, the next requirement was to filter the data by date. Instead of showing all records at once, it is more useful to specify a date range for the statement—typically the last month's transactions, for example. To illustrate this, I updated some sample dates in the dataset so that there would be data spanning different months, making it easier to see the effects of the filter.
Rather than applying criteria directly in the report, I recommend using a separate form to provide the date range for filtering. This kind of criteria form is very effective and user-friendly. You can create a dedicated menu for report operations that feeds the criteria into your report. If you have not seen how to get a value from an open form into a query, I suggest checking out my earlier lessons on that topic.
In my TechHelp free template, I already have a main menu form set up. Importing this menu into your database is straightforward. Once you have it, you can customize it by adding buttons that open relevant forms, such as the check register, and by providing fields where users can input or select their desired date range for the statement. If unnecessary buttons or status boxes are present, simply remove them to keep your menu clean.
Creating a button to open your check register form is simple by using the command button wizard—just select "form operations" and specify the form and relevant label text. Verify that it works as intended.
The next step is to add start and end date fields for filtering your statements. Label these accordingly. If you want these dates to default to the previous month's range, you can set up their control sources with date expressions that automatically calculate the first and last days of the previous month. For example, I point students to my separate tutorial on calculating date ranges in Access, which walks through using expressions like DateSerial for various scenarios. Simply replace specific parameters as shown, and you will have a form that always defaults to the last full month.
Once the menu fields for date selection are set up, all that remains is to have the report use those values when generating a statement. We will handle linking the form values to the report in the next lesson—so stay tuned for part 3.
If you want to explore the DateSerial function or advanced date calculations, I have several expert lessons and a full seminar dedicated to dates and times in Access. These cover everything from calculating workdays to handling holidays and reminders.
That wraps up part 2 of the account statements 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
Report design modifications in Microsoft Access
Implementing section-specific totals in reports
Adjusting report layouts for clarity and aesthetics
Simplification of report elements
Transposing items among report sections
Calculating net amounts by subtracting sums
Dynamically displaying titles based on data conditions
Adding functional text and labels in reports
Setting transparent outlines for report items
Incorporating date filters for statement reports
Linking forms to reports as criteria providers
Defaulting date selections to last month using expressions
Menu setup for report and form navigation
Creating command buttons for form operations
|
