Account Statements
By Richard Rost
2 years ago
Create Account Statements w Credit & Debit Sections
In this Microsoft Access tutorial, I will show you how to create account statements for a check register database, featuring separate sections for credits and debits with totals for each. Discover the steps to group transactions, utilizing queries and conditional statements without needing advanced VBA skills. Perfect for managing detailed financial records efficiently.
Kevin from Glasgow, Kentucky (a Silver Member) asks: I need to create weekly and monthly account statements. They need to have deposits at the top and issued checks at the bottom with a total for each. How can I create this and separate the two areas?
Prerequisites
Up Next...
Recommended Courses
Keywords
TechHelp Access, account statements, check register database, separating credits and debits, sub-report in Access, grouping levels in Access, Access query design, Microsoft Access report design, sorting checks and deposits, weekly account statements, monthly account statements, Access database customization, build customized reports in Access
Intro In this video, I'll show you how to create account statements in Microsoft Access by separating credits and debits into their own sections, much like professional bank statements. We'll talk about building a check register database, designing queries to organize credit and debit transactions, and setting up grouping levels in your reports for clear separation and subtotals. You'll also learn how to adjust report layouts, apply conditional formatting, add section headers using the if function, and prepare your statement for different reporting periods like weekly or monthly. This tutorial is great if you need clear, organized account statements in Access.Transcript Got a good one today, folks. We're going to make account statements. We're going to make a statement for your check register or whatever account you might have going on, and we're going to learn how to separate it just like you see when your checking account statements come and they've got credits separated from debits. Credits at the top, a little total, and then debits at the bottom in a different section, a little total. Then we'll total them all up, and that's an account statement.
Today's question comes from Kevin in Glasgow, Kentucky, one of my silver members. To paraphrase, Kevin says, "I need to create weekly and monthly account statements. They need to have deposits at the top and issue checks at the bottom with a total for each. How can I create this and separate the two areas?" Well, Kevin, that's what we're going to talk about in today's video.
It's easy to put together a statement. I've covered several different videos on that, but separating them into credits and debits, well, that's a little trickier, but it's not something we can't handle. There are a couple of ways you can do this. One way is to use a sub-report, and I cover sub-reports in "Access Expert 9," I think, but you can also do it with a grouping level, which is my preferred method.
So let's talk about some prerequisites first. This is going to be an expert level class, which means you don't need any VBA programming to do this, but it's a little bit beyond the basics. The database that I'm going to use for this video is the one that we built in the check register video, so go watch this if you haven't already and build that database unless you're a gold member, in which case you can download the database from that page, so go get a copy. And like I said, we're going to use grouping levels to create the credits and debits section, so go watch this video too. These are both free videos; they're on my website; they're on my YouTube channel; go watch them, and then come on back. Oh, one more. If you don't know how to use the if function, the immediate if function, go watch this too. This one's going to be important.
Alright. Since I am a gold member on my own website, I'm going to go grab a copy of the template from this video, and I'll save it on my desktop and open her up. Now, this database is actually about four years old. It predates the "TechHelp" free template. So, you won't find a lot of the stuff like the main menu in here if you've been following along with my videos these past couple of years. But that's okay. We're going to actually steal the main menu from the other database in just a few minutes. But let's go ahead and see what we've got to do today.
Alright, here we got a check register table, and in this database, it's got ID, check number, a check date. Now, these can be any transaction. They don't have to be checks. Since this is a check register database, I did check number and check date. But that's the date of the transaction, and whatever identifier you want to put on it, that's fine. A description, whether it's clear or not. Debits and credits, I decided to track as two separate fields; that's fine too. You can easily put them together later. It's also just as valid to put an amount field and put a negative value for debits, whichever way you want to handle it, it's fine. This is just how I happen to handle this particular database.
Now, the form is that, it's pretty straightforward. There's also a check register report that comes with it. Now, the hard part is separate. We're going to take this, basically take this report, and separate it out so debits are in one section and credits are in another. So what we're going to do is we're going to make a query, and the query is going to group them together using an if function.
Alright, so let's close these down and let's go create a query where we can group these things. So let's go to create, query design. I'm going to bring in my check register T, close that. Now, I'm just going to bring all the fields in and the first thing I want to do is I'm going to create a header for my different sections, whether it's credits or debits. Let me zoom in so you can see this. Let me fix my window here. We're going to call this thing the title and it's going to be if. Now, how do I tell if it's a credit or a debit? Well, look at either one of the fields. You should only have a value in one of those two fields. You have a zero in one field, credit or debit, and then a value in the other field. So we'll just look at credit. So if credit is greater than zero, then this is going to be credits. This will be the credits section header. Otherwise, it's debits. Okay? And yeah, if you're worried about it, you could program another logic to make sure that there's a value in one of those two fields. Only one can be zero, and the other one has to have a non-zero value. That can be done with validation rules. There's all kinds of things you can do, but we're assuming that this is how the database is set up. Okay?
Okay. So now if I run this, I can see which ones are debits and which ones are credits, and that will make a nice grouping level. Okay?
Now, one more thing I also need is this: it's in two separate fields here. I want to put this in one field. So I have a simple 'amount' field for whatever section I happen to be in. We can also do that with an if function. I'm going to copy this one, copy, paste it over here, and I'll zoom in, shift F2. Alright, we'll just make this say 'amount'. Okay, now if credit is greater than zero, then this value here should be the credit value. Otherwise, it should be the debit value. Right, that'll be the amount of the transaction, whether it's a credit or a debit. See, that thousand from the credits go here, this one, the debit goes here. See? Nice and simple. Now I got one field to worry about in my report because it's going to be in two separate sections. Alright?
Alright. Let's save this bad boy as check credit debit q. Okay. Now we have a query on which to build our separated statement report. Let's close this and let's go and copy this report here. We'll just make a copy of this guy. Copy, paste, control C, control V. We'll call this the check credit debit R. And let's go and design this guy, design view. First thing I'm going to do is change where he gets his value from. So double click here and we got a little thingy here. What does it say? "I have a report width is greater than the page width." This comes up a lot. Basically, you just gotta make sure that this doesn't go past like whatever your margins are set. I talk about this in a million other videos. We're going to fix this in a minute. For now, let's make this go to the other query. So we're going to get our values from check, credit, debit q, that query that we just made. Okay.
Now in here, I don't care about seeing the running balance. Get rid of that. What else don't I care about? I don't care about the cleared amount. Now, if you only want to see cleared transactions on this statement, you could very easily put that in here if you want. Just add 'is cleared' to this query, uncheck it so you don't get two of them, and then make the criteria here true. Now this statement will only show cleared transactions. That's up to you. In fact, I think I'm going to leave it like that. I like that. Now, if you run this, you're only going to see cleared transactions. If you want to see both, that's fine too. If the purpose of this is you want to compare it with your bank statement, you obviously only want to see the cleared stuff, so completely your choice, do whatever you want to do. So likewise, in here, I don't need that cleared checkbox like that. We can make this a little bit shorter. We can bring this in, which will get rid of that margin width problem. And how am I set up here? Let's see, we're going to be portrait. Okay, that's fine. The other one, I think, I meant to make it landscape. Like I said, it's four years old, folks. I don't even remember making this database. That's why a lot of times you guys send, like, I've been putting stuff on YouTube since what, 2008? You guys send me questions about my older stuff. I'm like, I've got to re-watch the video now. I don't remember making that.
Alright, so let's see what this looks like. Save it, close it. Let's right-click and open it and print preview. I know it's below the bottom of the screen there, but you get the point. Okay. Alright, looks pretty good. Okay. This is just the cleared transactions. Now we got to separate them between credits and debits. Okay. So we'll do that with some grouping levels, a grouping level, and a sorting level. Alright. So, group and sorts right here under report design. We're going to add a group. We're going to drop this down and say with the footer section. Okay, so now I've got a title header and a title footer. In addition to that, I also want to add a sorting level. I want to sort by check date underneath the title. So you're going to get all your credits up here and then sorted by date, then all your debits, and then those will be sorted by date as well. If you don't specify a sorting level, it will just be sorted whatever random order that access decides to give them to you. Usually, it's whatever order they were added to the table, but not necessarily. So we are done with this grouping level. Remember click this guy up here to close the pane, not that. That guy right there deletes that level, which we don't want to do. Just click that to close that sorting level. So now in the title header, make that a little bit bigger, the title header section, this goes above each of those groups. This is the group for the title. We're going to add a field, what am I going to add, the title right there. Get rid of the label that comes in with it and I'm going to take this title and make it really big, like big, not like crazy stupid big, but maybe like 26 points and then bold. It's going to say credits or debits. So save it, let's see what we got. Right click, print preview. Oh yeah, okay, looking good, looking good. There's all my credits. There are all my debits. Some formatting to do. The next thing, let's get rid of the borders around this. We'll get rid of this stupid gray background here. Remember, we turned the alternating colors off for some of the sections, but not for the new section we just created, the group level.
Alright, so right-click, design view. Let's pick this guy. Let's go formatting, shape outline, transparent. Click on this, double-click on it, go to format, alternate back color is nothing. This guy down here, title footer, nothing. Okay, yeah, I know my phone's going off in the background. Ignore that, pay no attention to the man behind the curtain, folks. Alright, let's see what we got now. Right-click, print preview, looking better, looking better. Now I left a little bit of a gap here. Why? Well, because this is up in the page header. I don't want that there. Let's put it down here in a new group section. Design view, I'm going to take you, snip, control X. We don't really need a page header unless you want to put something up there. Paste it down here and then click and drag and line them up right there. If you want to put a line in here, that's fine too. I like to make room before I put a line in. Find the line tool, which looks like a chart, but the line tool drag it right there and drop it perfect a little bit to the left. Looks good. Looks good slide this up and now save it, close it, right-click preview. Whoo-hoo, it's getting there. Start to look sexy. Oh, yeah. Okay.
Let's see, now a couple of things we got to change still. These totals down here aren't right because it's still just adding all this up. We'll fix that. We'll put some title across the top here. And most importantly, we need a way to limit the dates in here because right now we're just getting everything. So we need to be able to throw some kind of a criterion here. It says I only want to see this month or this week. That's what Kevin's looking for right, monthly and weekly accounts do that too, and we'll do that in tomorrow's video, so tune in tomorrow, same bat time, same bat channel. Or if you remember you can watch it right now because I'm going to record it right now, but 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 tomorrow for part two.
TOPICS:
Creating weekly and monthly account statements
Separation of credits and debits in statements
Building a check register database
Utilizing Access' grouping levels for report design
Utilizing if functions for section headers in reports
Building queries to organize data by credit and debit
Designing reports based on custom queries
Setting up grouping and sorting in Access reports
Adjustment of report layouts to fit page settings
Formatting reports in Access
Adding and modifying report sections in Access
Using line tools for visual elements in reports
Handling date criteria for reporting periodsQuiz Q1. What is the primary goal of the video tutorial?
A. To teach how to use the VBA programming language
B. To discuss the benefits of becoming a gold member on the website
C. To demonstrate how to create segregated account statements with credits and debits
D. To review basic functions of database software
Q2. Who is Kevin and why is he mentioned in the video?
A. A silver member asking about weekly and monthly account statements
B. The host of the video tutorial
C. A guest speaker from Glasgow
D. The developer of the software being used
Q3. What must viewers do before attempting the tutorial discussed in the video?
A. Update their database software to the latest version
B. Watch specific prior tutorial videos and possibly build or download a database
C. Subscribe to become gold members
D. Learn advanced VBA programming
Q4. Which method does the video presenter prefer for separating credits and debits in account statements?
A. Using a sub-report
B. Using a standard report without any grouping
C. Using a grouping level in the database
D. Editing each transaction manually
Q5. How can you determine whether a transaction is a credit or a debit in the database, as mentioned in the tutorial?
A. By checking if the value is positive or negative
B. By seeing if the transaction is less than zero
C. By using an if function to check the amount in the credit or debit fields
D. By manually sorting each transaction
Q6. What was a key feature the instructor used to create the report separating credits from debits?
A. A specific type of loop in VBA
B. An if function to assign values to a newly created field
C. Advanced SQL queries to automate data retrieval
D. Direct modification of data in the database
Q7. What additional functionality did the instructor decide to include in the account statement report?
A. Filtering transactions to only include those that are cleared
B. Automatically transferring data to a separate ledger
C. Integrating external financial data for comparison
D. Utilizing cloud storage for backup of the statements
Answers: 1-C; 2-A; 3-B; 4-C; 5-C; 6-B; 7-A
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 will focus on how to build an account statement in Microsoft Access that separates credits from debits, just like the statements you receive from your bank. The goal here is to display credits at the top of the statement with their own subtotal, then list debits underneath with their own subtotal as well. We will then calculate the total for the entire statement at the bottom.
This topic was inspired by a question from a student who wants to create weekly and monthly account statements in Access and needs to separate deposits from withdrawals, providing subtotals for each. Let's walk through how to organize and present this data in a clear, professional manner.
Creating a simple account statement in Access is relatively straightforward, but breaking out the report into distinct credits and debits sections does require a bit of planning. There are a couple of ways you can approach this problem. One is to use sub-reports, but my preferred method, and the one I'll demonstrate today, is to use grouping levels within a standard report. This method is clean, efficient, and does not require VBA programming. However, it does require a bit more knowledge than basic Access reports, so some familiarity with queries, calculated fields, and report design is recommended.
For this demonstration, I'll be using a database that was created in a previous check register video. If you're unfamiliar with the check register database, I recommend watching that introductory lesson first and building the sample database yourself. If you're a gold member, you'll be able to download the template directly from my site, which can save you some time.
Let's review how the database is structured. The check register table includes an ID, transaction date, check number (though this could be any kind of transaction), a description, a flag for whether the transaction is cleared, and two fields for debits and credits. In my example, credits and debits are tracked separately, but you could just as easily use a single amount field and use positive for credits and negative for debits. Either approach works, so you can adjust this for your own needs.
There's a straightforward form for data entry, along with a simple report that lists transactions. Our task today is to take that base report and split it into two clear sections for credits and debits. To do this, we will start by building a query that classifies each record and aggregates the data appropriately.
The first step is to create a new query based on the check register table. Bring all of the table's fields into the query. We want to create a field in the query that will act as a section header - to label each transaction as either 'Credit' or 'Debit'. Since each transaction should have a value in only one of these two fields, just check if the credit field is greater than zero. If it is, label it as a credit; otherwise, label it as a debit. You can easily adapt this logic if you use a single amount field, but for my example, I'm sticking with two fields.
It's also helpful to create an 'amount' field. This field will show either the credit or the debit amount for each record, depending on which section it belongs to. This will make your report design easier since you'll only need to use one calculated field to show the transaction amount in your grouping sections.
After saving this query, you now have a foundation on which to build your report. Start by making a copy of the existing check register report in your database. In the report design view, point the report's record source to your new credits and debits query.
At this stage, feel free to remove any unnecessary fields from the report, such as running balances or the cleared checkbox unless you specifically want them on your statements. If you're only interested in displaying cleared transactions (which is likely if you want to reconcile with your bank statement), you can include a criterion in your query that only pulls cleared records. This makes your report mirror your official bank records more closely.
Next, set up the grouping and sorting necessary to separate credits from debits. In the report design, add a grouping level based on the title field you created in your query that labels each transaction as either 'Credit' or 'Debit.' Make sure to include a footer for the group so that you can display subtotals below each section. You can also add a secondary sorting level for the transaction date within each grouping so that the statement shows transactions in chronological order within each section.
In the grouping header, add a large, clear label that will show whether you are in the credits or debits section. Format this header to stand out on the page. Adjust your report layout as needed to ensure everything fits on the printed page. Remove any default borders and background colors that you don't want, and fine-tune your report's appearance by disabling alternating colors in your new grouping sections.
You may wish to reorganize your page header or other labels depending on how your template is set up. If you want to visually separate sections, you can use the line tool in the report designer to add clear dividers between headers or sections.
The next step is to add subtotals for each section, which you can do in the group footers. Ensure that your summary fields add up only the amounts in each respective group, so credits get a subtotal and debits get a separate subtotal. At the end of the report, include a final total if needed.
One more thing to consider for practical use is adding criteria to your report so that you can produce statements for specific periods, like one week or one month. You can add date range prompts or controls for this, which is something I will cover in part two of this series.
That is the process of separating credits and debits in an account statement using Microsoft Access, utilizing grouping and sorting in report design, calculated fields in your queries, and report formatting tools. This approach allows you to generate professional, clear statements that mirror what you would get from a bank or accounting system.
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 weekly and monthly account statements
Separating credits and debits in account statements
Building a query to group credits and debits
Using if functions for section headers in queries
Combining debit and credit into a single amount field
Designing reports using custom queries
Setting up grouping levels in Access reports
Adding sorting levels to Access reports
Customizing report layout and page settings
Formatting reports and sections in Access
Adding and modifying group headers and footers
Removing borders and backgrounds in reports
Inserting lines as visual separators in reports
Filtering reports to show only cleared transactions |
