Show Debits & Credits
By Richard Rost
18 months ago
Separate Debits & Credits From One Field in MS Access In this Microsoft Access tutorial, I will show you how to separate debits and credits from a single transaction amount field, where debits are entered as negative amounts. We'll cover creating a new transaction table, making calculated fields in a query, and displaying totals in a form footer. Trevor from Redmond, Washington (a Platinum Member) asks: I recently watched your Account Statements video where you show how to create a report that separates the debits from the credits. However, you have debits and credits separated in your table already. I just have one transaction amount field. Is there a way to separate that where it shows debits and credits separately? Where debits are entered as negative amounts, how would I split those? MembersThere is no extended cut, but here is the database download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, separating debits and credits, transaction amount separation, split transaction amount field, Microsoft Access queries, IIF function in Access, sum function in forms, continuous form calculations, update check register table, Access database tutorial
Transcript
Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. In today's video, I'm going to show you how to separate debits and credits from a transaction table so you can show them separately in the form footer.
So if you've got a check register table and you've been entering everything as amounts, your checks out are debits, so they're negative, and any deposits are showing as positive, but it's all in one field. I'm going to show you how to separate that out so you can show a total of the debits and a total of the credits. And then you can do other cool stuff too.
Today's question comes from Trevor in Redmond, Washington, one of my Platinum members. Trevor says, "I recently watched your account statements video where you show how to create a report that separates the debits from the credits. However, you have debits and credits separated in your table already. I just have one transaction amount. Is there a way to separate that where it shows separately?"
Okay, the video that Trevor is talking about is this one, my account statements video, where I show you how to take your credits and debits and put them together as separate sections. Usually, you'll see that your bank does this at the end of your statement. It'll take all your transactions, and at the very end, it'll say here's all the credits and the credit total, here's all the debits and the debit total.
But the database that I'm using, I've got those fields separate. I've got a debit field and a credit field, and I just add them together to give you an amount field. So what Trevor is saying is he's got the reverse. He's got all of his transactions in one field, either positive or negative, and he needs to be able to split them apart so you can show what I was showing in the account statement.
Okay, I'll show you how to go backwards in this video, and that's fine. There's nothing wrong with that either way. One of the things I talk about in the check register video is that it's perfectly fine to store your data either way. It doesn't matter whether you use one field or two.
So this will be an expert-level video, which means it's a little bit beyond the basics, but you don't need VBA programming, so you don't have to be a developer. That's what I consider expert. You should know how to create calculated fields, both in forms and in queries. You should know how to use the if function, and I call it "IF." I know I'm being silly with it, but that's why you remember it, so it's IIF, immediate if, not if if if. I don't know how many times when I was first learning this I used to always type it backwards. So you always hear me say "IF."
And you should know how to make form footer totals. If not, go watch these other videos first and then come on back.
Alright, so here I am in my TechHelp free template. This is a free database you can grab from my website if you want to, but you don't need to. We're going to create a new table since the other check register database is already built wrong, well not wrong, but opposite of what we're trying to do. We'll just make a new table real quick.
So we'll go to create, table design, we'll just base this on any old transaction table. So transaction ID, that's my auto number. We'll do a transaction date, that'll be a date time. Remember, don't use just the word date as a field name. That's a reserved word. So you want to have something date, even if it's just my date, that's fine. Don't use just date, just like you don't use just name.
Okay, down here in the default value field I'll put equals date. That'll put today's date in there by default on new records. If you want the date and time you can use now down here. Usually for transactions like a check register I don't care about the time, I really just want the date. Optionally if you're doing checks you can put a check number in here. All right, you can make that a number or text, whatever you want. I've seen some people use this for transaction codes that might sometimes have letters in them, so if that's the case, use short text. I'm going to just stick to numbers since I'm doing primarily a checking account. Now I'm going to make the default value null, so I'm going to get rid of that zero. That way if it's not a check, if it's like a deposit, it'll just be blank, it won't have a zero there.
Alright, the amount, that'll be a currency. We'll do a description which is short text and I always like to have notes which is long text in case you want to put like, you know, this was for grandma's new hip or whatever you put in there. Save this as my transaction transaction table primary key y'all transaction ID and if you don't know any of the stuff that I just covered watch my access beginner level one class. It's free. It's four hours long. It's on my website. You'll find the link down below. Alright, let's close this and just put a couple sample records in.
So we've got, let's say here, an opening deposit. So I'll put in here $1,000, opening deposit. Maybe make this a little bigger. There you go. Alright. Next up, we got dollars and that's cat litter for data someone's beaming at all I don't think that's what that by check number 102 let's say it's a whole lot for that I forgot now we have to we have to take into consideration to remember that since we're using one field that is a check that has to be a negative number if you put a negative value there.
Okay. That's one of the reasons I personally like using two fields, but that's up to you. Okay. So negative $75 for a tooth sharpener for quark or wharf, they use them too. Let's do $300 winnings at Dabo. And then one more check. We'll do 103 which was 20 bucks for phaser practice. Okay. Alright, so we got enough in here to see what we're doing. Alright. So let's close this. Save changes y'all. Now I like to make a query that does the calculations. You can do a lot of the calculations that you need in forms, but sometimes you can't. Sometimes if you try doing the totals like this by separating things out in a form, especially a continuous form, it doesn't like it if you try to total them down in the footer. So instances like this where you've got calculations that you want to do sums and stuff on in the form footer, make a query. Trust me, it just makes things a whole lot easier than trying to do calculations directly in forms.
So let's create a new query and this query is going to be based on my transaction table that I just made and we can bring in all the fields. I can close this guy. Alright now right here we're going to make two calculated fields using the if function. If function right? Debits, which will be transaction amounts less than zero and then credits which will be transaction amounts more than zero. Where do zeros go? I don't know. Why are they even in there in the first place? Who knows? All right, so I'm gonna zoom in shift f2 just so you can see this better. Alright, so debit is going to be if Amount is less than zero then put the amount value here. Otherwise put a zero here. Okay, and I'm gonna copy that I'm gonna ctrl C it because credit is very similar click here shift f2 Paste that in we're just going to change this to credit and then all we got to do is flip this little guy right here just like that and complete with the sound effect to make sure you forget when you do that don't forget the sound effect it's very important access won't work if you forget your sound effect.
Alright let's save this as my transaction queue for for query and then we'll run it and now we don't need this. Now you can see right there there's your debits and your credits. Look at that. Now your data looks just like mine did before so you can plug this into the account statements database if you want to generate that report where they're split and you can go away from here. Let me show you how to put this into a form footer and do calculations on that. At this point it's pretty straightforward. So we're just going to use, I got my continuous form here from my template. If you haven't watched how I built this TechHelp free template, there's a video on this too, but I like to create, see these little guys here with the little underscores in front of them? Those are just so I can use these for making new ones. So I'll copy paste.
Alright, we'll call this my transaction F for form. And it's now down on the bottom here. There you are. Open it up in design view. Alright. Make this guy bigger. This is going to be wider than that, so let's make it wide. And I like to leave these guys in here just for formatting purposes. So now I'm going to first open up the properties for the form, change the record source to my transaction queue. That's where you're getting your data from. You want those fields in the debit and credit field so you can do your sums on them. Okay. Once you're done with that we can close that. Go to add existing fields. I'm gonna bring everybody in except debit and credit. You don't need those fields here on the form unless you want to see them. If you want to see them, great. Leave them on there. But you don't need them. As long as they're in the underlying query underneath, right, what the form is based on, you can still do sums and totals on okay that's the rule so I'm gonna bring all these guys in drop them right there except you notes we don't need you goodbye I'll we're gonna do something with notes in just a second okay all right get rid of all these labels why I'll show you why in a second transaction ID can go over here and that's the reason I leave that little gray field around it's just for formatting.
I click on this, I click on the format painter which if you don't have it up here in the quick launch toolbar it's on the home tab. Format painter. Oh now it's gray. See that? And these guys I'll just line up. Transaction date can go there. Probably about that big. Check number can go there. You don't need to be that big at all. Alright the description we'll put here. And then we'll slide the amount over on the right. Okay, now I got these guys in here. I can get rid of these. Alright, we'll slide these guys up like that. And then, now you can make a separate label for all these, or you can just do my little trick where I just make one big label. I'll leave ID over there, and then for the rest of them, I'll just do date, and then spaces, and then the check number and then spaces and then description and then a bunch of spaces and then I'm out. That's just easier than making a bunch of labels. Okay, slide this bottom up, save it, close it, open it, let's see what it looks like.
Okay, looks pretty good. Forgot to left align everything. I like everything left aligned. I know accountants like everything right aligned. We'll leave, that's one of the reasons I put the amount over here on the right. We'll leave that amount right aligned. So come in here, we'll select all of this stuff. We will left align it and then we'll right align the amount, which means you gotta try to line up that amount over here on top of it. Looks pretty. Leave a little extra space for us, for the scroll bar in case you need it. Oh, maybe there. Alright, let's see. Save it, close it. I keep wanting to click on the customer form. That's where I do most of my stuff. Alright, open it up. Looks pretty good.
Okay. Now, let's put some totals in the bottom down here. So I'm just going to copy amount, copy, click in the footer, and then paste, and it puts it down there. Alright, yeah, I know it's in the upper left-hand corner. We're going to copy it and slide it over here. And then I'm going to go paste, paste two more times. See how they appear down below there. Alright, that's pretty cool. Alright, slide these up just a touch. Okay, this first one is going to be the amount, but just the total. Alright, so we'll double-click on you. The control source is going to be equals the sum of amount. So it's equals sum, the sum function, and then in parentheses, amount. And give it a good name too. We don't want to call it just text 66. I'm going to call it sum amount. Don't confuse those two things. This is an actual function. Equals sum amount, the amount field. This is just its name, sum amount. And that's a RIC thing. That's my standard. That's what I do. Okay that will sum up all of the transactions here.
Now for these I want the debits and the credits. Whichever order you want. I don't care. So here this one's going to be equals the sum of the debits. Now look it shows up in the record source for this form. So you can use it. Alright, and up here we'll say sum debit. And we'll do the same thing for the credit field. So right here, equals sum of credit. Not credit, just credit. And sum credit like that. Save it. Close it. Close it. Close it. Open it. Boom. There you go. There's your total total, the total of your debits, which looks about right, yep, one hundred and, yep, okay, and the sum of your credits right there. A little confusing, let's add some color. Now you can use conditional formatting if you want or you can use the format property.
In fact, I've got a video, where is it? This guy, it's a beginner video and I show you how to put different formats in here, right, based on just the value in the field. And if you go to my website where this guy is located, I conveniently put it right down here for you. Just copy this. What it does is this is the positive value, right, and I put the thousand separator in there. If it's negative, it'll be red. If it's zero, it'll be dash, dash, dash, and green. And if there's no value, if it's null, it will be blue. And say the word none there. Alright, so we're going to copy that. We're going to come back over here. We're going to select all four of those fields and open up the property sheet. Or you can right click and go to properties. Where it says format right there, it's currently currency, you're going to just paste that formula in there.
Boom, right there. Well, it's not really a formula, it's a format. Alright, it looks just like that. And you can change this. You can put an actual zero on there, put a zero there. There you go. Okay. Hit okay. Close it. Save it. Open it. Boom. How's that? That's a little more easy on the eyes. Okay. And you can see they're nice and separated down there. What about those notes? If you like the notes, I like to put long text fields down here in the form footer. Remember, anything in the footer, if it's not a total or another aggregate function, right, like a max, min, average, one of those things, if you put just a field down here, it's linked to whatever record you're on. And I've already got a field called notes right there that's got a nice pretty formatted text box that I can just borrow. So I'm going to copy you, copy, close this now, come back over to you, right-click, design view, click in the footer, and hit paste.
All right, and then we'll just maybe move it around a little bit, make it size to this form like that. That looks good. This is where you can put the notes for your check or whatever you want for the transaction right there.
Okay, and if you click on it, it's called notes and it's bound to the notes field which there happens to be a notes field in your table. That's why I like to use the same fields if they do the same thing for different stuff. Like you might have product notes, you might have customer notes, you might have order notes, call notes. That's fine, you can just reuse the fields everywhere. You know what table it's from because you can just call it CustomerT.Notes.
All right, save that, close it. Let's open it back up. All right, now if I click on cat litter for data and put in here, you know, so spot has somewhere to poop, whatever. If I click on another record, it goes away, but if you click back on this one, you can see it comes back, because this field is bound to this record, all right? That's a little tricky, you can put stuff in the footers.
All right, so you don't have to waste all kinds of space up here and top in the form. Okay, I could go on about this stuff for hours and hours, but this pretty much shows you how to do what I promised I was gonna show you how to do.
If you like this kind of stuff, this is exactly the kind of thing that I teach in my Access Expert lessons. There are 32 levels. I go through all kinds of stuff, including formatting and query functions. I got a whole section on just the different functions, if and rounding and you name it. I cover it.
I'll put a link to this down below and if you're interested in learning how to do more with the check register type stuff, I've got a whole seminar on it. It teaches you everything you want to know about making a transaction or check register for your access database, including printing checks, you know writing out the you know, five dollars and 42 cents that kind of stuff. All kinds of reports and graphs and charts and things with molecular structures and moving parts, and you name it, it's in the seminar.
So check it out, I'll put a link down below 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 next time.
TOPICS:
Separating debits and credits in a form footer
Creating a new transaction table
Setting default values for date fields
Using IIF function for calculated fields
Creating calculated fields for debits and credits
Building a query for transactions
Summing debits and credits in a form footer
Formatting calculated fields for readability
Adding notes to form footer linked to records
COMMERCIAL:
In today's video from Access Learning Zone, I will show you how to separate debits and credits in your transaction table and display them separately in the form footer. If your check register table has all transactions in one field, I'll guide you through splitting them to show totals for debits and credits. We'll cover creating a new table, setting up a query for the calculations, and then adding these to a continuous form with footer totals. This tutorial is expert-level but doesn't require VBA. You'll need basic familiarity with calculated fields and the IIF function.
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. What was the main goal of today's video tutorial by Richard Rost?
A. To create transaction tables.
B. To separate debits and credits from a transaction table.
C. To build a new database from scratch.
D. To write VBA programming code.
Q2. What is the purpose of separating debits and credits in this tutorial?
A. To sum up both fields together.
B. To show them separately in the form footer.
C. To use them for backup purposes.
D. To combine them into one field.
Q3. According to the tutorial, which action is recommended before entering new transactions for fields such as 'date'?
A. Use just the word 'date' as a field name.
B. Use a different field name since 'date' is a reserved word.
C. Manually input all dates.
D. Use abbreviations only.
Q4. What should be used as the default value if you want today's date in the new records?
A. now()
B. datetime()
C. date()
D. getDate()
Q5. What is suggested for creating calculations for transactional data in forms?
A. Directly calculating in the forms without queries.
B. Using VBA code exclusively.
C. Performing calculations in a query.
D. Avoid using queries for calculations.
Q6. Which function is used to differentiate debits and credits in the transaction table?
A. SUM()
B. IIF()
C. COUNT()
D. AVG()
Q7. What does the IF function essentially check in this tutorial?
A. Dates and times.
B. Description fields.
C. Whether the transaction amount is less than or greater than zero.
D. Transaction IDs.
Q8. How are the debits and credits stored in the transaction table originally set up by Trevor?
A. In separate fields.
B. In one single field as either positive or negative values.
C. As text entries.
D. As part of the description.
Q9. What important aspect should be considered when using reserved words such as 'date' or 'name' for field names?
A. They should be accompanied by prefixes or suffixes.
B. They should be written in all capital letters.
C. They have to be abbreviated.
D. They are not allowed at all.
Q10. What did Richard recommend for creating new transactional tables?
A. Only use predefined templates.
B. Create them in VBA exclusively.
C. Design them in query mode.
D. Create new tables using the table design feature.
Q11. In the tutorial, what formatting option is provided to help differentiate positive and negative transaction values visually?
A. Using different fonts.
B. Conditional formatting for color coding.
C. Changing background images.
D. Formatting using VBA programming.
Q12. What is the purpose of the 'notes' field as mentioned in the tutorial?
A. For storing additional information related to each transaction.
B. To keep backup information.
C. For storing transaction IDs.
D. To store formatted currency values.
Q13. What should be done to add functionalities like sums and totals in form footers, according to the tutorial?
A. Use static text fields.
B. Employ calculated fields in queries.
C. Utilize multiple transaction tables.
D. Bypass the use of footers for sums.
Answers: 1-B; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-B; 9-A; 10-D; 11-B; 12-A; 13-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.
|
