Account Balances 1
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 1
This is part 1 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
In this video, we'll set up the tables for our database, and build a couple of queries to calculate account transaction totals and available balances.
Members
There is no extended cut, but here's my database:
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!
Prerequisites
Suggested Template
Links
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, track microsoft access account balances, personal finances, daily spending transactions, track finances, balance chart, credit and debit, banking activity, credit card balances
Intro
In this video, we begin a new expert-level TechHelp series on tracking account balances and day-to-day transactions in Microsoft Access. I'll show you how to set up the foundational tables for account types, accounts, and transactions, create sample data, and build aggregate queries to calculate transaction totals for each account. We'll discuss the differences between storing data in Excel versus Access, talk through the design process, and look at how to handle issues like joins and formatting in queries. This is part 1.
Transcript
Today we're going to see how to track our account balances and our day-to-day transactions in Microsoft Access. This is part one of an expert series. I don't know how many parts there will be. I haven't planned them all out, but we're probably going to make a bunch of them. Now, this video is meant to follow my Excel videos on account balances, so go watch those, even if you only watch the beginning of part one, just so you get the gist of what this database is going to be for and why you'd want to use it. I don't want to repeat all that information here, so go watch those. I take the first five minutes and explain the whole point of this database.
Basically, for your day-to-day transactions, you can see your balances. It is not to replace your monthly reconciliation with your checking account and your credit card statements or that kind of stuff. It's just so every day you can take a look and see what your balances are, what your outstanding transactions are, and you don't run into a situation where you forgot about something. Then your account's $10,000 negative or whatever. Go watch those first, even if you only watch part one, then come on back.
Now, I am going to call this a developer video, even though in today's video, part one, we're not going to do any programming. We're just going to set up the tables and a couple of queries, but eventually we're going to be getting into some programming. You can start building this if you're not a developer and you can get some use out of it, but this is really focused on development with some VBA programming once we get into the later parts, probably as early as part three, I'm going to guess. I don't know. I haven't laid this all out yet, so I'm not sure yet.
If you want to learn some VBA before we start the VBA in this series, go watch this intro to VBA. It's about 20 minutes long. It teaches all the basics in about 20 minutes. Also, go watch this video on aggregate queries. We're going to use this today to get a sum total for all of our transactions for each account. This is very important. Go watch this. And there's one more video, but I'm not going to ruin the surprise. There's a quiz coming up, so I'm not going to tell you what else to go watch.
Here we go. To start off, I have not pre-planned this. Normally I sit down and I run through the database once quickly in Access before I record a video for you. But in this particular case, I'm not going to, so the mistakes that I make during the video will be mistakes that you probably will make, so we're going to just make them together.
I opened up the Excel sheet in the actual Excel application instead of the web version so I can look at it closer here. We're going to need a couple of different tables. One, we're going to need an account table. Two, we'll need an account type table. Could you use a helper table for these things? Yeah, you could, but since this is going to be a very simple and small database, I probably wouldn't bother. We're going to need a transaction table for all this stuff. I think that's it. I think just those three tables will probably work.
These will be stored values. This and this will be calculated values. This, we're going to store, but we're going to update it when the data is updated, so that will be fine. As far as queries go, this will be calculated in a query. I'm going to make a form for this and a form for this, and then we're going to load those both as subforms into a big parent form so I can have these both side by side nice and big. Or maybe not. Maybe we'll do it as two separate forms, and I'll have this one open to the right of this one. We'll figure that out when we get there. Like I said, I haven't run through this yet.
I've been thinking about doing it for a while because I still, honestly, use an Excel sheet for this for my day to day. I've been wanting to make an Access database for myself, so as I make this, it's going to be for both you and me. I'm probably going to want to add some cool stuff for myself, and if it's relatively easy stuff, I'll put it in the regular video. If it's crazy, cool, fancy VBA stuff, I'll put it in an extended cut. Most of the first couple of videos are probably going to be mostly beginner or expert level stuff, but there's going to be code spread throughout, so that's why I said it's going to be a developer series.
Now, the nice thing about Excel also is that Excel is really good for prototyping before you move to Access. A lot of times, for my business and for me personally, I will generally start something in Excel. Once it gets to a point where I'm using it regularly, or it gets complicated, or I see there are going to be a lot of relationships for different things, then I'll move it up to Access. But for really simple stuff, I'll keep it in Excel.
This is something that for me personally, I have probably 40 different accounts and wanted to track transactions, so I've been to the point where I've been wanting to move this to Access myself for about a year now. It's good to have a blueprint here right now in Excel for what I want it to look like. You can do that with pretty much any project. You're going to start something, you just throw it together quick in Excel, and when you get it down like, yeah, this is what we want, then you move it over to Access. Excel is sometimes easier to give you the big picture.
I'm going to move this guy out of the way, slide it over here to the right. I'm going to use my TechHelp free template for this, not because I'm going to use the customer and contact and all that stuff, but I've got all the stuff in here that I like to have for most of my databases, so we're just going to delete this stuff when we don't need it at the end.
For those of you who follow along, I did change for my regulars who watch all my videos in order as they come out, I did make a new change. This is template version 1.93. It's available on the website on the template page or the blank template. What I added was, and I covered this in a video a little while back, I added the stuff from this video. Remember this from the loyalty logo, right, checks to see if it's trusted? If you get the security warning, because sometimes people will email me and say, "Hey, I downloaded your database off the website, but it's telling me that it's a security risk," and all that stuff. So then I tell them go watch this video, create a trusted location, etc.
What I did was I got rid of this as being the startup form, which is under File, Options, Current Database. I set no display form, but I set an autoexec macro. That checks to see if the current project is trusted, and it opens the main menu. So then the main menu's code can run. If not, it opens up NotTrustedF. NotTrustedF pops this message up, which is a little more friendly for the user who doesn't really know what's going on: "Looks like you're running from a not trusted location." You click on this and it takes you to that page.
Hopefully this saves me from having to answer all those emails, except for all the people who have the older version.
Anyway, total digression, total side point, but I know some of you follow every video and you want to know what's new in this newest version. You can keep a table in here with the changes, but I stopped it.
Where are we? Oh, yeah, account balances. I'm going to close the main menu now. I'm not going to use it. We're going to repurpose it later. Let's create the tables that we're going to need.
So, let's create table design. I like to start off with the bottom-most level tables. We're going to need an account type table. AccountTypeID. AccountType. Then I'm going to try text, and I can't think of anything else that's going to be needed for this. You can add notes and all that stuff. Yeah, I think that's good. Just save as AccountTypeT. Yes.
Let's put some data in here. Now, I know in the other video I did just "bank," but in my personal one, I keep checking and savings different. That's just me. If you want to put bank, that's fine. Credit cards. I've got investments and loans like mortgages. I like to note at any given time how much I've got available in checking, how much is in my long term savings, and so on.
That should be good for that. Save changes, yes.
Now we're going to create the account table. Create table design.
AccountID - Autonumber.
The AccountName - text.
The AccountTypeID - number, type long integer.
I like to keep my IDs tops on the side there. My first two things that I always think about on a table are always the AutoNumber and then the name of that table, like whatever it is. Then I always remember I need a foreign key, so I always put it in there and then I slide it up. That's the order that my brain works in, that I remember it in. You'll see me do that in a lot of videos.
You may want to store an account number in here, which I'm going to make text also. Even though it's called account number, I know the banks call it account number. Usually it is just a number, but sometimes it starts with a zero, so you want to store it as text. Sometimes I've got some account numbers with letters in them from different institutions.
If you want to store a routing number in here, you can, like for your checking account, savings account, whatever, doing transfers, etc. If you want to save your, you know, CVV code and all that stuff, that's up to you. We're not going to get into the security aspects of Access here. Remember Access isn't very secure. Even if you go through all the stuff in my security seminar, it's still only about 99 percent secure. So unless you're using SQL Server or some real server platform, I would be very careful keeping financial data in Access. I mean balances and account numbers and amounts, that's not really that big of a deal, but you start getting into security codes and stuff like that, it can be an issue.
I had one person because I did a check register thing and I used my actual checking number in my seminar one time, and the guy was like, aren't you worried? I'm like, not really, because anybody that you write a check to gets your routing number and account number. If I send you a check, you've got my account number. If they use it fraudulently, I just contact my bank, which is one of the reasons I don't use checks that much.
Now, I'm going to stick with the same fields that I had before: credit, balance, pending. Those would be the three amounts you put in there. It's up to you if you want to use these same ones that I do. That's what I like. I've been using that for years, so I'm going to stick with it. Credit is a currency. Balance is a currency.
Now, we've got the transactions and the actual balance, and those are calculated fields. We don't put those in the table. The date, we're going to put DateUpdated, that'll be a date. That is going to be calculated, but I'm going to store that in the table because when the record is changed, we'll update it at that point.
You can put a default value, but actually, I'm going to skip putting a default value in there because I might set up my accounts on one particular date, but I might not check them all right at first. So I want this to be the actual date that I verified with the bank or the credit card website that balance was correct. I'm going to save that for that.
Now, we are going to add some fields later on. I like to have a minimum balance. I know I mentioned in the other video it's $500, but honestly, for me, it's different for several different accounts. Like, for one of my accounts, I want the floor to be a thousand. The one that I use a lot. Other ones I want to be different values based on that account. Some accounts I don't care that much about. I have a balance, you know, a minimum of like 200. So we're going to make a field later to store that minimum. We'll do that in a future video.
For me personally, I also keep my upcoming expenses in an Access database, so I might add that to this. I'm not sure. I might make it a separate video. So I have another calculated field called "Need in two days," which says, hey, you've got these things coming up from your expected expenses in the next two days. So we'll see, maybe I'll add that. I'm not sure.
But I think for now, that's good. You're always going to have fields you're going to want to add later. That's just how it is.
Save as AccountT, primary key, yup.
Let's put some sample data in here. I need my account types so I can see what they are. Let's just go with the same sample ones that I used over here. It's always better to build your forms with sample data.
So first is PayPal. That's checking. I'm going to treat that as a checking account. PayPal. I don't know an account number for that or a routing number. Let's put a thousand in it, and I have a pending of negative 20, and today's date. Same trick works in Access, by the way: Control semicolon.
Next was Regions Checking. So that's a checking account. I'll just put a sample, you know, whatever, 444, routing number, okay, 1500, and that's all for that. This, by the way, will get filled in in the form when we update it.
Next, we have Regions Savings 2. Again, some random numbers, 2000.
Oh, yeah. Amex Credit. Credits 3. Put a number in here, account number. I started with a 3, blah blah blah. Your max, this will be credit for a credit card. It represents your maximum credit limit. So your credit line, whatever that happens to be. Balance, let's see. I got 1200 on that. Pending is 400.
After that, IRA is going to be an investment. Fidelity, IRA. I don't know what the account is, whatever, just put in here 10,000.
Finally, the mortgage is a loan. And of course, if you have multiple mortgages, put on there with a property as however you want to distinguish it. I have one, that's all, and let's put in here the balance is negative. I know that's not my balance, just putting in a balance. I wish that was my balance.
So here we've got our accounts, our account types, all that's good. Now we're going to set up our transaction table.
I'll keep this open for now. Oh, one thing I did forget from this table is my order, my custom sort order. I forgot about that sort order number. I'll just leave it there, save it. Now let's put it at the top, and put our sort order. These are in the order there, and now that I want, sort is going to go one, two, three, four, five, six. Okay, I'm going to save for new items. Let's make the default value, well, we could set it something really high, like a thousand. Or you could flip this if you want, because you don't have to see this in Access. You do your custom sort order. You can hide this, and you can just make little buttons to move up and down on what you want. That's how you want to do it. I'll leave it like this for now, this is fine. We're not going to get too crazy in the first couple of videos.
But now we're ready for our transaction. So, create table design. This will be my TransactionID. We'll put our TransactionName in here, and that's just something you can type in whatever you want for that particular transaction. But you're still going to link it to an account, so AccountID, that's your foreign key. We'll put our SortOrder in here, that'll be our number. And then the amount of the transaction, so Amount, that'll be a currency value. Finally, let's add in here a DateUpdated, that'll be a date.
Save it, close it, and we're ready to put some sample transactions in.
Let's do the same ones we had over here. When it comes time to actually make these, I'm going to make a form, a special form to add these. That's going to have two spots on it, that's going to have a spot for the money coming out and the money going in. But for now, in the tables, we'll just put both of them in here manually.
So we got payment to Amex. Now here are the account IDs. This is where Excel is different from Access. We need to know the ID. The account ID for Amex is going to be four. The sort order on this one, I don't use this one as much, but you could still keep it in here if you want to. I put a sort order in for ones where, like this, I'm going to keep these transactions on the screen all the time, and then just add new ones down on the bottom. These are the ones that I use a lot. But I'm even thinking, see, like I said, I haven't done this in Access yet, I'm even thinking I might not need this because these are just going to come in from the bottom and float up. As they clear, we'll just delete these and we'll hit a button to add new ones. I don't know, we'll see how it works. For now, I'm just going to ignore the sort order here, but the field's there.
So this was, let's make this, let's see, 150.
Payment. Here this again, Access doesn't copy like that as easily. The from account is Regions Checking, so that's going to be account ID 2. We'll do 150 negative.
Transfer to IRA. Again, copy-paste. So the from account is Fidelity IRA, that's going to be 5, and the to account, excuse me, the to account is 5, the from account again is going to be 2, and let's say this was 500.
That's enough for now.
The reason why I want to get these in here was because now we can build a query to calculate this transaction amount. And then this guy here.
Now, you might be tempted to use a DLookup or a DSum or formula inside your query, but it's better if you stick with aggregate queries. Trust me, it's just faster. For a database this small, with only maybe a few dozen records, it's not a big deal, but if you start getting into thousands and thousands of records, yeah, it's going to slow down. Or if you're going over the network.
If this were an actual financial tracking database where I wanted to keep a history of all this stuff, I wouldn't just delete these transactions when they clear. If you're doing, like, a check register or whatever, you want to, like in my check register database, click a box to show that it cleared instead of just deleting it. But again, this is just for day by day, so I don't really need it for tracking historical transactions. If that's what you want, add a cleared check box, yes or no, on here, and then just don't show the cleared stuff on your main menu.
A lot of times what I do in situations like this when I'm just starting to build a database, I will take screenshots of this, because as we go into making queries and forms, I don't want to have to stop and look up what the names of these, the fields and stuff, were. So we got our account type, this guy here is easy, I'm just going to screen capture this. I use HyperSnap, but you can use Windows Snip if you want, whatever works good for you. Paste that in here. There's that, in Paint. Microsoft Paint comes with Windows.
Then I've got my account table, which is this. I'll drop that in here. Here's my account table. And then my transaction table, just so I've got it, I can visually look at it. Paste that in here, and I'm going to slide this over. I got a big screen, sorry. Slide this over here so it's not in the way, and I can refer to it. I can just look at it while I'm recording. Zoom out a little bit so you can see. Here's my Access database, this is the window I'm working on, there's my Paint where you can see all my stuff, and it's just right in my face. That's easier to see. I used to have four small monitors and I recently upgraded to one big monitor, and I like it a lot better.
Now I'm ready to throw together that aggregate query. We're going to aggregate the transactions based on the AccountID, and we're going to sum up the amount, then we're going to link that to this. If you haven't done aggregate queries yet, go watch this video that I mentioned in the prerequisites.
I can close these, I've got my little screenshot over there. Create query design. We're going to be aggregating our transaction table. Group by the AccountID, let's go to query design, change this to a total query. Group by the AccountID, sum up the amount. Save that, let's call this TransactionTotalQ. Run it. There's each account, and how much is in that particular account for transactions. Notice that Account 2 is my checking account, that's got the money coming out of it. That's right. And then the other two have money going into it, that's perfect, that's exactly what I want. See how this is going to be a lot faster than using a DSum inside a query? The aggregate query just processes once. DSum has to process once for each record, it's got to look through the whole table every single record, whereas Access processes an aggregate query internally a lot faster.
I'm going to change SumOfAmount here, let's alias that. We're going to call that TransactionTotal. I don't like when it says SumOfAmount. Perfect. Save it. I'm going to drop the sum in my little screenshot gallery that I've got going on. Now I can take this TransactionTotal, make a query to link AccountT with TransactionTotalT, and I can put over here that TransactionTotal from this.
Ready? Create query design. I'm going to bring in my account table and my query TransactionTotalQ. Notice they're joined AccountID to AccountID. Let's bring in all the fields from over here. I only like to bring in the star from one table, so over here we're going to bring in TransactionTotal. That should give me a list of all my accounts with the totals for the ones that have transactions. Let's run it and see what happens.
That's right, but I'm not seeing all of them. The TransactionTotals here are correct. Regions Checking should be 650. Why am I not seeing all of my accounts? Take a second, think about it. If you know, post a comment down below right now what the issue is. I'll come back here so you can see what is the issue. Why am I not seeing all of the accounts? Pause the video and post a comment if you know.
Did you get it? The answer is my join type. This is an inner join. An inner join means only include rows with the join fields from both tables or equals. So in other words, for everyone over here you have to have one over here. For accounts that don't have transactions, you're not going to see them. So we have to pick left outer join here or left join. That puts a little arrow there. Now we'll see all of these and the transactions where they're matching.
There's all my accounts and the ones that are missing over here don't show up. I didn't list this in the prerequisites because I was going to quiz you on it in the middle of the video. Honestly, I didn't think of it before, but that's why it's not in the prerequisites now. Normally, I go back and rerecord the intro after the video, but in this case I wasn't planning on that so I'm going to stick it in now. But it won't be in the prerequisites. If you need to learn about outer joins, go watch this. You'll find a link down below in the link section.
Now, you might want to see them even if they don't have a value; you might want to see a zero there. That's up to you. You can throw an NZ around this. See? There's your NZ. But now it unformats it. I'm going to do that. Let's call this TransactionTotalX. You can't use TransactionTotal twice, so let's just call this X for now. I'm going to say comma zero, and we're going to format this whole thing as currency. Format this, comma, currency. Let's see what this looks like.
Notice, though, my numbers are still lining up to the left. I don't like that because that tells me that this is still going to be treated as text. So instead of formatting it as a currency, we're going to convert it to currency. CCur, convert that to currency. Big difference. Run it, and now let's see. Now that's what I want.
The reason why I've got the TransactionTotalX is you can't use TransactionTotal twice. I want this to be called TransactionTotal here, so let's go. Let's save this, first of all, as AccountQ. Notice also, by the way, that once we link in that aggregate query, we can no longer add new records in here. That's a big deal. We'll deal with that later, though.
TransactionTotalX, I don't want that to be called that. So I'm going to go back to this TransactionTotalQ, and let's get rid of that alias. Let's just call it Amount. We'll deal with SumOfAmount here because we're not going to see this amount here. Then close it.
Go back to our AccountQ. Now, one nice thing about Access is it will change that automatically, so it puts SumOfAmount in there, and now I can put this back to just TransactionTotal. That's what I want, there, that looks good. Notice how it's lined up on the right side of the cell. Let's call it the cell, the right side of the column, whatever you want to call it. It's a field in Access, I know. Old habits are hard for Excel users. They'll always call this a cell.
But now that I've got this and I've got these, now I can calculate my actual balance. Actual balance is going to be Credit plus Balance plus Pending plus TransactionTotal. The other ones should have zeroes in them; we shouldn't have to worry about null values there. These all have zeroes in. There's our actual balance when all is said and done.
I like to add this to my screenshot so I've got this stuff in here. I don't usually bother with the stuff at the beginning because I know that's just part of the table, but now I've got the query. This is what we'll base the form on where we can display all the accounts.
That's going to do it for today's class. In my room we'll pick up where we left off and we'll start working on the forms. No more queries. That's all for part one. In my room part two, we'll start working on the forms. I don't know how many parts there's going to be, but I've got lots of stuff I want to do, so we'll see.
That is going to be your TechHelp video for today. I hope you learned something. I hope you had some fun. Live long and prosper, my friends. I'll see you in part two.
Quiz
Q1. What is the main purpose of the Microsoft Access database being built in this video series?
A. To replace monthly reconciliations with banks and credit cards
B. To help see day-to-day account balances and outstanding transactions
C. To serve as a full accounting replacement for businesses
D. To securely store all financial data including security codes
Q2. Why does the instructor suggest watching the Excel account balances videos before continuing with the Access tutorial?
A. Because the Access database uses identical formulas from Excel
B. To understand the context and goal of the Access database being built
C. To learn advanced VBA skills required in this video
D. Because Access cannot track balances without Excel
Q3. Which of the following tables are created as part of this Access database setup in Part One?
A. BankTable, CustomerTable, TransactionHistoryTable
B. AccountTable, TransactionTable, AccountTypeTable
C. VendorTable, InvoiceTable, PaymentTypeTable
D. ContactTable, AddressTable, ReportTable
Q4. When designing the Account table, why is the AccountNumber field set to a text data type?
A. To allow phone numbers to be included as account numbers
B. Because Access does not support numerical fields in tables
C. To preserve leading zeros and accommodate account numbers with letters
D. To make sorting by account number easier
Q5. What is the instructor's recommended approach for setting up calculated fields like balance and transactions?
A. Store all calculated values directly in the table for quick access
B. Use Excel macros for calculations inside Access
C. Calculate values using aggregate queries instead of storing them in tables
D. Only use DLookup functions for calculations
Q6. What is the key benefit of using aggregate queries instead of DSum or DLookup for transaction totals?
A. Aggregate queries only work on small databases
B. Aggregate queries do not require relationships between tables
C. Aggregate queries process faster because they calculate sums in a single pass
D. There is no difference in performance between DSum and aggregate queries
Q7. If you want to show all account records even if they have no transactions, which join type should you use in the query?
A. Inner join
B. Right join
C. Cross join
D. Left outer join
Q8. Why does formatting a value as currency sometimes cause it to be treated as text in Access queries?
A. The Format property always converts to text for display
B. Currency fields can never display as numbers in Access
C. Field alignment is random in Access
D. The CCur function converts text to left alignment
Q9. What is a suggested method for custom sorting of accounts in the instructor's setup?
A. Rely only on account names for sorting
B. Use a SortOrder numeric field in the Account table
C. Sort using the account balance field
D. Manually sort records each time the form is opened
Q10. In the video, what is suggested as a good general workflow for moving from Excel to Access for a project?
A. Always start building in Access, then export to Excel if needed
B. Use Access if you need basic calculations, and switch to Excel for relationships
C. Prototype and design in Excel, then migrate to Access for regular use or complex relationships
D. Skip prototyping and create the full product in Access directly
Q11. What is the purpose of the DateUpdated field in the Account table?
A. To store the date the account was opened
B. To record the last date the balance was verified for accuracy
C. To display the date of the last deposit only
D. To be used as the primary key for the table
Q12. What is the warning given about storing sensitive financial information in Access databases?
A. Access is completely secure if you set a database password
B. Storing any information in Access is prohibited by most banks
C. Access is not fully secure, and one should be careful with security codes and sensitive data
D. Access encrypts all data by default
Q13. If you want to exclude records marked as 'cleared' from a query or form display, what is a good approach?
A. Delete the cleared records from the table
B. Add a Yes/No (Boolean) field called 'Cleared' and filter on it
C. Sort the records by transaction amount
D. Only display records with transaction totals greater than zero
Q14. What is the formula given in the video for calculating the actual balance for an account?
A. Credit minus TransactionTotal
B. Balance plus Pending plus TransactionTotal
C. Credit plus Balance plus Pending plus TransactionTotal
D. Credit plus TransactionTotal only
Q15. What is the demonstrated advantage of using aggregate queries for summing transaction amounts when building the queries?
A. They are only useful for displaying totals in forms, not in tables
B. They let you calculate a sum for all accounts in a single, efficient query
C. They reduce the need for referencing data types in tables
D. They can only be used with single-table databases
Answers: 1-B; 2-B; 3-B; 4-C; 5-C; 6-C; 7-D; 8-A; 9-B; 10-C; 11-B; 12-C; 13-B; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will focus on how to track account balances and daily transactions in Microsoft Access. This is the first installment in an expert-level series, and while I have not determined the total number of parts, there are plenty of topics I plan to cover as we progress. This series builds upon my earlier Excel lessons on account balances, so I highly recommend reviewing those, even if it is just the introduction to part one. There, I explain the overall purpose of this database and why you may want to use it. Rather than repeat all that information here, please take a few minutes to watch the beginning of those Excel videos before continuing.
The goal of this database is to help you monitor your day-to-day finances—so you always have a clear picture of your current balances and any outstanding transactions. This is not intended to replace your monthly reconciliation routine with your bank or credit card statements. Instead, it provides a daily summary so you do not find yourself caught off guard by forgotten charges or unexpected negative balances. Be sure to watch those prerequisite Excel lessons for a better understanding of the database's function and its value.
While I am labeling this as a developer series, in this opening segment we will not be writing any code. For now, we will concentrate on setting up the necessary tables and a couple of queries. Down the line, we will dive into VBA and more advanced programming, probably as soon as part three. If you are not a developer, you can still follow along and create a useful tracking tool in Access, but the focus will shift to development and VBA in future installments.
If you are interested in learning some VBA before we get there, I recommend going through my introductory VBA tutorial. It is about 20 minutes long and covers all the basics you will need. Additionally, you should watch my lesson on aggregate queries, as we will use that today to calculate the sum of transactions per account. Aggregate queries are essential for efficiently tallying up large numbers of transactions. There is another video you will need soon, but I will keep that a surprise for now; there is a quiz coming up, so stay tuned.
Today's project is unscripted. Usually, I pre-plan the entire process and run through everything ahead of time. This time, I am working through it with you, complete with any mistakes along the way. I opened my reference Excel file in the desktop version for easier viewing. We are going to need a handful of tables: one for account types, one for your accounts themselves, and one for transactions. You could technically use a helper table for account types, but since this is a relatively simple database, I will keep the structure straightforward with just these three tables.
Some of the values we will store directly, while others will be calculated. For example, a field like the transaction total will be a calculated value, produced by a query. I intend to build forms so we can work with these records easily. I am considering loading the forms as subforms side by side in a main form or perhaps opening them separately. I have not decided on the final layout yet, and we can adjust as we go.
This project is something I have wanted to build for myself for a while now. Like many of you, I use Excel for daily account tracking, but as my financial life has become more complex, the need for an Access solution has grown. You may find that starting such a project in Excel is ideal for prototyping; once you know what you need and the relationships become more complex, Access becomes the better tool. Excel remains my go-to for quick setups, but when you reach a certain scale or need more structure, making the switch to Access makes sense.
I will be using my TechHelp free template, not because I intend to keep the included customer and contact features, but mainly because I like the prebuilt setup and will remove unnecessary elements later. For those interested in updates, this is template version 1.93, which you can find on the website. An improvement in this version involves checking whether the project is running from a trusted location, so if you get a security warning, the database now prompts you in a clearer way and guides you to the steps needed for proper setup.
With that background out of the way, let us get to creating the tables needed for our database.
We will start by defining the AccountTypeT table. It will store different types of accounts such as checking, savings, credit card, investment, and loan. In my personal setup, for example, I keep checking and savings separate, though you can combine them if you prefer. Should you wish to add notes or other fields, feel free. But for now, AccountTypeID and AccountType are sufficient.
Next, we create the main AccountT table with fields for the unique AccountID, AccountName, AccountTypeID as a foreign key, and an Account Number stored as text. Even though account numbers are usually numeric, you will want to store them as text to preserve any leading zeros or include occasional letters. You may also want to record a routing number or other optional info, remembering that while Access databases are only about 99 percent secure, storing balances and non-sensitive account identifiers is generally acceptable.
For financial tracking, I maintain Credit, Balance, and Pending fields—all currency type fields. These track the credit limit (for cards), current balance, and pending transactions for each account. Some fields, like the actual running balance, will be calculated in queries later. We also include a DateUpdated field to indicate the last time the account was verified. Rather than use a default value, I prefer to manually update this field when I verify an account balance, so the data remains accurate.
I plan to add other fields going forward, such as a minimum balance amount, since this value can differ between accounts. For now, we are starting simple, and additional fields can be added as necessary.
With the two supporting tables set, we can input example data. Using accounts like PayPal (treated as a checking account), Regions Checking and Savings, Amex credit card, Fidelity IRA, and Mortgage, you will see how each fits into the broader table structure.
I realized I omitted a SortOrder field for custom ordering of the accounts. I recommend adding this field for easier management and display. You can hide it from view and add move-up/move-down controls later if desired.
The next table is the TransactionT table, containing fields such as TransactionID, TransactionName, AccountID (as the foreign key), Amount (as a currency), DateUpdated, and optionally a SortOrder field. Transactions will be entered manually for now, but later I will develop a form to simplify their entry, including support for representing money moving out and into different accounts.
Sample transactions might include a payment to the Amex card, a transfer from checking to IRA, and so on. If you are used to Excel, note that you must bring in the appropriate AccountID from the reference table in Access when entering these transactions.
At this point, I want to demonstrate how to build a query that calculates the transaction totals per account. Aggregate queries are significantly more efficient than using DSum or DLookup, especially as your data grows.
One important distinction with this database is that it is designed for daily review rather than maintaining a long-term register. If you wanted to keep a historical record, you would modify this approach and perhaps include a checkbox for transaction clearance rather than deleting them as they clear.
For convenience, I like to take screenshots of my tables so I have an easy visual reference for field names as I build queries and forms. A tool like HyperSnap or Windows Snipping Tool works well for this. I create image references for AccountTypeT, AccountT, and TransactionT to make the process smoother.
Now, it's time to build an aggregate query that groups transactions by AccountID and calculates the sum of the Amount for each. This query will provide a total per account. If you are unfamiliar with aggregate queries, be sure to check out my tutorial on that topic.
After setting up the TransactionTotalQ query, I verify the results: each account's total transactions display correctly. Summing at the query level is always faster than recalculating with DSum or similar functions for each record.
I then adjust the naming of calculated fields with aliases to improve readability and presentation. For accounts with no transactions, I prefer to display a zero rather than a blank. Wrapping the calculated field in the NZ function achieves this but may affect formatting, so using CCur to cast the value as currency restores appropriate display alignment.
Next, to display all accounts along with their transaction totals, I use a left outer join in another query (AccountQ). This ensures all accounts are shown, even those without transactions. Many people initially get tripped up here using an inner join, which excludes accounts that lack associated transactions. If you want to learn more about outer joins, there is a recommended video linked on the website.
Finally, I add logic to calculate the actual balance by summing the Credit, Balance, Pending, and the calculated TransactionTotal fields. This logic provides a real-time look at your accounts, which you can build into your forms for easy daily review. This query will form the basis for a user-friendly form displaying your financial summary.
That concludes today's lesson. Next time, I will show you how to work with forms to build on the progress we made here. There are plenty more features and enhancements to come as the series continues.
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
Purpose of the account balances tracking database
Creating the AccountTypeT table
Populating AccountTypeT with sample data
Creating the AccountT table
Populating AccountT with sample data
Storing account numbers and routing numbers
Adding credit, balance, and pending fields to AccountT
Choosing proper data types for account fields
Storing and updating DateUpdated for accounts
Including a custom sort order in AccountT
Creating the TransactionT table
Populating TransactionT with sample data
Using AccountID as a foreign key in TransactionT
Setting up currency fields for transaction amounts
Adding DateUpdated to TransactionT
Building an aggregate query for transaction totals
Grouping and summing transaction amounts by AccountID
Aliasing and formatting query fields
Creating an outer join to show accounts without transactions
Using NZ and CCur functions in queries
Linking account and transaction aggregate queries
Calculating actual account balances in a query
Understanding limitations of queries for adding records
Preparing queries as data sources for future forms
|
