Expense Tracker
By Richard Rost
3 years ago
Track Your Recurring Expenses in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you how to track your recurring expenses. We will enter the expense, the date you last paid it, and its frequency, such as monthly, weekly, quarterly, annually, etc. Then, the database will calculate the next due date. You can sort the list based on what's upcoming. We'll also add some conditional formatting, allowing you to see past-due items in red, items due in the next few days in yellow, and all items in good standing in green.
Members
Members will learn how to integrate this database with our Account Balances database. We'll include a button that, when clicked, processes the recurring transaction, adds the amounts into the account transaction table, and then updates the dates accordingly.
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
This video follows my Account Balances series. While it would be helpful if you have watched those videos first, it's not necessary. I also released an Excel version of this video, which could be useful to watch first, but again, it's not required.
Suggested Template
Next Video
Recommended Courses
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, Microsoft Access, Expense Tracker, Recurring Expenses, Database Tutorial, Expense Management, Access Database, Financial Planning, Personal Finance, Money Management, Access Tutorial, Expense Tracking in Access, Conditional Formatting in Access, Track Expenses, How to Use Microsoft Access, Managing Expenses in Access, Financial Organization, Access Database Design, Recurring Transactions, Budgeting Tips, Microsoft Access Tips and Tricks, Account Balances Database, Automatic Transactions in Access, Expense Tracker Setup, Budget Management
Intro In this video, I will show you how to build a recurring expense tracker in Microsoft Access. You'll learn how to set up tables for your recurring transactions and payment frequencies, calculate next due dates using the DateAdd function, create a form for entering and viewing expenses, and use conditional formatting to highlight when bills are overdue, coming up soon, or paid in full. We'll also incorporate sample data and set defaults for common scenarios, making it easy to manage payments like rent, utilities, and subscriptions all in one place.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today, we're going to build an expense tracker. I'll show you how to track your recurring expenses, whether it's stuff that happens weekly, every other week, monthly, quarterly, or whatever. Your grocery bill, paying the electric, your car payment, your mortgage, all that stuff. We'll put it in the expense tracker.
You'll put in the date you last paid for it and the frequency - is it monthly, daily, or whatever, how many days, two weeks, one week, three months, that kind of stuff. Then the database will let you sort it by when it's next due. It'll show you red if it's overdue, yellow if it's due soon, and green if you're cool.
That's what we're covering in today's video. For those of you who have been following along with my Account Balances series that I just did in the Extended Cut for the members, later on we're going to integrate this into that database. You can hit this button here to mark it paid and add those transactions to your transaction list in the Account Balances database, which is why I built the expense tracker, because I want this for myself.
So here we go. First off, if you haven't watched the Excel version of this video, go watch that first. I'm going to show you how to do your recurring expenses in Excel first. You have your expense, the name, the account that it comes out of, which we're going to tie into the Account Balances database, the amount, the date that you last paid, quantity, the frequency - is it monthly, daily, yearly, right - then we're going to calculate the date that the payment is next due. So go watch this video first so you get more of what we're doing now.
I am going to use the database that I built in the Account Balances series. It was a nine-part series that I just finished. You don't have to use that if you don't want to, but I am going to use the account table and some of the forms that we built in that one.
Is it necessary to go watch it? No, you don't have to, but you'll get a little more benefit out of it if you do. Definitely go watch my DateAdd video though, because we are going to use the DateAdd function today and this is what's going to control how we calculate the next date that this is due. So go watch this. These are all free; they're on my YouTube channel and on my website. Go watch them and come on back.
So I'm going to open up the Account Balances version 0.09. This is the database as it was after the ninth part of the Account Balances series. Let's open this guy up here.
Now, for those of you who are not members, you may see some little extra things here and there that I've added in the extended cuts along the way, but you can just ignore that stuff.
This is a split database and usually I say go to the back end and add these tables and then we'll link them back in. But we're just going to build these tables in the front end for now and then we'll move them later.
We've got our saved transaction table. These are saved transactions, ones that you can manually say "okay, run this one again." But we're going to make another one for recurring transactions - things that happen every month, every week, every day (you got stuff you have to pay every day - I had a loan like that once, I had to pay it daily, weird).
Let's start off with a recurring transaction table. So, Create - Table Design. This will be RecurringTransactionID, and I'm going to copy Recurring Transaction to my clipboard, we're going to type it a couple times. That's my AutoNumber.
RecurringTransactionName, that's basically the description of it. Remember, we don't ever use "Name" as a field name - that's a reserved word.
AccountFromID, that's going to be a number. That's a foreign key pointing to an account table. This is where we're going to get a list of our accounts from, where each transaction is coming out of.
We've got Amount, currency. We've got DateLastPaid, that'll be a date.
A FrequencyID.
Now, unlike in the Excel video where I just put an "M" or a "D" or a "Y" in the column, in Access, we're a little more sophisticated here. We're going to actually make a frequency table.
For those of you who are a little more advanced, could I use a helper table for this? Yes, I could, but I'm not going to. Helper tables I usually throw into databases where I'm going to have lots and lots of different little tables. This one's not going to be that database - it's only going to have a couple little things. I'm not tracking people in here where I have prefix and suffix and mister and junior. We'll make a frequency table, it's okay.
We have FrequencyQuantity or QTY, that'll be a number and I'm going to default that to one. So when you add a frequency like month, the default will be one month; if you make this day, the default will be one day. You don't want to have this zero, so you don't have to keep changing it.
If you want to put Notes in here, put Notes in here. I always still go to type "M" for Memo. How long are they going to make this change? Notes, by the way, is pretty much the only field that I will still make plural. I don't know why, it's just an old habit. I try to make everything singular.
Let's save it. This is another reason why I copy and paste it: RecurringTransactionT. Primary key, that's that guy. Let's close it and let's set up our frequency table while I'm thinking about it.
Create - Table Design. We've got FrequencyID. I'm going to put a SortOrder in here. That'll be a number and that's just so you can manually order them because the default order is going to be day, month, year and they just happen to be in alphabetical order: D, M, Y. But what if you add something else in there, like weekly? Well, it should be before month but after day, so that way you can manually control the order they appear.
Description and the DateAddCode, short text. What is that going to be? Unlike in Excel where we had to have multiple conditions and a nested IF statement to handle it, depending on which - day, month, or year - in DateAdd, we've got this nice little set of codes we can use instead, where we can just plug that into the DateAdd function as the interval. "yyyy" for years, "m" for month, "d" for day. If you want to put weeks and quarters, you can. I don't; I'll put seven days instead of one week. But if you want to in your database, that's how you want to put the Legos together, put the Legos together that way.
These are what's going in that field. This way we don't need anything crazy, we just plug that data in here. Save this as my FrequencyT.
Let's put some data in here. So, one will be day and that's just a "d". That should be one there. Two will be month, "m". You can put week in there if you want to. I'm skipping week for now. Three will be year, and remember - year is "yyyy", not single "y". Single "y" is day of the year, a number from one to 365, or 366 if you're in a leap year. So, there's our frequencies: one, two, and three - day, month, year.
Let's put some sample data in our recurring transaction. Give me just a couple records. Nice to have a little bit of sample data.
So, recurring transaction, and I'm going to open up my account table below it so I can see what my account numbers are. Let's say we've got the mortgage. The AccountFrom - where are you paying this from? I'm paying this from my Reader's Checking Accounts, so AccountID 2. Let's say it's $2,000 (I wish). DateLastPaid - how about 6/15. FrequencyID, this is monthly, so that's two (day, month, year) and the FrequencyQuantity is one.
Next up, how about the electric bill. AccountFromID, let's say I pay it with my Amex, so four. $400 a month. DateLastPaid, 6/20. Frequency 2 and 1. See how this works? Pretty straightforward.
Close that, close that, save it. We're done with the tables.
Now we can calculate the next due date in a query by taking that frequency information with the date last paid and then, boom, there's your next date.
So, Create - Query Design. What are tables? Bring in your RecurringTransactionT and your FrequencyT. Now, just in case you have any ones in here that don't have a frequency, we're going to make this an outer join. It shouldn't happen, but just in case, I would in fact make that required.
Let's see if I can do that in design. FrequencyID - required, yes, that's got to be required. This should be required, yes. And I'm going to make DateLastPaid required.
I know I preach a lot about not having required fields, but that's in a case where it's like you have an employee who just doesn't feel like typing in a customer's middle initial or something like that. But in this case, in order to add this record you've got to have a date. We'll default it to today's date; they've got a starting value. That way we don't have to worry about this. First of all, we don't have to worry about nulls all over the place if they're missing values; they just can't not put a value in there.
So again, the things I teach, they're just rules of thumb. They're not hard set in stone. I try to avoid "required" if it's just to prevent lazy people from putting bad data in, but sometimes you do want to use required.
In this query I'm going to bring in the star from this side, give me all these fields. I actually am not going to bring any fields down from here, but you can use these fields in calculations. Right here, we're going to calculate our next due date. I'm going to zoom in so you can see it better.
The next due date is going to be DateAdd as my DateAdd function. First thing is the interval, and that's going to be the DateAddCode - that's why I put the code in the table. Then the FrequencyQuantity, then the DateLastPaid. Make sure those all have brackets around them and Access didn't put quotes around it. Usually if it sees it as a field it doesn't put quotes around it, sometimes it messes with you.
So there's that, save it. This will be my RecurringTransactionQ. Run it, and there we go, look at that, there's our value. Make sure you have the new row on the bottom because we do want to be able to add to this at the form level.
Let's throw another one in here. Let's do pest control from Account 2, let's say it's $150 every three months and I just did it today. So FrequencyID's going to be month, Quantity is three. There we go - three months from today.
Disney annual pass. That'll be from my Amex and it's $730. Let's say I paid it on March 1st. The FrequencyID is three, it's annual, and it's every year. Perfect.
So, see how much easier that was than doing it in Excel. Unless you guys know an easier method in Excel, that's the way I've always done it. Excel has a DateAdd function in Excel VBA, so you could make a function out of it in VBA, but as far as regular built-in Excel functions, no.
Now it's time to make the form out of this. I'm going to borrow the transaction form because a lot of what I need is already in there. So let's copy the transaction form - right here, copy you - and this will be the recurring transaction.
Design view. First up, change the record source: RecurringTransaction. Let's change the caption: Recurring Transactions. I like to change the color right off the bat too, so that I know what I'm dealing with. Let's make Recurring Transactions like a reddish color because I always like to equate them with money that I'm losing. Good enough, we're not going to need Cleared.
So the transaction name is going to be RecurringTransactionName. The account ID is going to be the FromAccountID, and up here it's going to be the AccountFrom combo. This should keep all the format you put in here too. Amount is the same, and then DateUpdated is going to be DateLastPaid.
Get rid of the saved transactions. I actually leave the button there for now, we're going to need some buttons in a minute.
Next up, we need our frequency stuff. So this is going to be the frequency, and we get a combo box for that. Go to Form Design, drop this down, get a combo, drop it there. Get the value from a table or query, we're getting the values from the frequency table. We need FrequencyID and Description. We're going to sort by - oh, we've got to bring in the sort order too, bring in SortOrder.
We don't need the DateAddCode here. Next we're going to sort by the SortOrder. We don't need to see the sort order so hide that so now there's two hidden columns.
That can be nice and small too, and you can even, if you want to, put in here just D, M, and Y - that's up to you. In fact, now that I'm thinking about it, I like that. Let's cancel and go do that. I like that very much. Get rid of that, save this form, and let's go back to our table.
Design view. Let's call this Abbrev - ABBREV. I like to make abbreviation "ABBREV" so I don't have to type out "abbreviation" every time. Save that, and we'll just put in here D, M, Y. You could do the first character, or the description, whatever. There are a lot of things you could do, but if you have some different stuff in there it might not always match up.
Now I can make that combo box. Where are you? Let's start over. This is part of the design process, by the way. You're never going to just lay everything out perfectly. I always teach in like Access Beginner 1, write everything out on paper, put all your tables together, all your queries, all your forms, draw them on paper if you can. But honestly, you're going to be in the middle of building and you're going to want to change some little thing that you didn't plan for, so you have to stop and go back. The key is to try to catch as much of that stuff up front as you can, because if you have to make a major change to a table afterwards and you've already got all kinds of stuff based on that table, it could be a problem. But I just want to let you know that me stopping and canceling and going back, that's normal. What developer doesn't do that from time to time?
Look up table/query, we want the Frequency table again. What fields do we need - ABBREV and Description. Let's put the abbreviation first, then the description. You'll see why in a second. Next, sort by the SortOrder. Next, hide the SortOrder. We'll make the abbreviation nice and small like that so this is what you will see when you drop the box down, and then this is what you'll see when the box is closed.
Next. So we're picking a frequency, the ID is the bound column. We're going to save it in the FrequencyID. Next. What label do you want? Doesn't matter, we're going to delete it anyway, hit Finish. That's my reminder to delete the label.
Rename the box to FrequencyCombo. Now we can make this nice and small. The combo box button is bigger than the data in it. That should be about right, right about there. Save it. Let's see what it looks like.
RecurringTransaction form. That's not too bad; we could go a little smaller if we want, but that looks fine.
Let's right-align it to get a little more space in there. It looks like this isn't quite on the thing. See, that's why, because this guy came in just a slight bit too big. You can always see that, see right there, it's just kind of slightly below. Anything you bring off the toolbox, you have to resize.
Let's right-align you. We need the frequency amount, which is the FrequencyQuantity. Delete this, let's do this and that, and this is next to it like this, and we'll just do Frequency, one big box, like that - the number in the thing. Let's see what that looks like, save it.
Again, I got so used to just hitting the Accounts button in the last series. There we go, that looks good - one month, one month, three months. I like that.
Now for the date, let's change these labels up here. This should be the From Account; transaction name. This is the Date Paid. We're going to copy this guy because I want the form, I don't want to just change the form, adding a little bit. Copy, paste, bring you over. This will be NextDue.
Now you are going to change to NextDueDate. Save it.
Let's keep this around for the conditional formatting anyway. Let's get rid of the conditional formatting on Date Paid. I don't care how long ago I paid it; if it's an annual thing, that date's going to be a year old. What I care about is the next date that it's due. So let's go to Conditional Formatting and just delete these rules.
Now, let's see what your rules are. These are going to be a little different. Let's go with the same rules we had before. If it's less than today's date, we'll use this one: "Field value is less than today's date" - you are overdue. Move to the top.
Next is going to be yellow: "Your value is less than today's date plus seven," so due within the next week. Same thing with this guy. We're going to go "less than two weeks" and this one, let's go "greater than or equal to 14" for the rest of them, none of them will be white then. So, "greater than or equal to date plus 14."
So, we should be: less than today - it's overdue; due within seven days; due within two weeks; and then due in more than two weeks. Apply, OK, let's see what it looks like.
It looks good. These are past due; today's the 27th.
Let's add a few more. Let's say I've got to add my weekly savings deposit, and this will be from my Regions checking, $100. The frequencies are coming in - do we have a default value for the frequency? I don't think we do. That's what's happening, there's a zero in there by default. Let's make that default monthly. I'll set it now. Well, it was weekly, so days and then seven. Let's say that I made this deposit last on 7/25.
Let's fix our tab order. Go to Design View, tab order, auto. OK. You are not a tab stop, all right, set that to no. All these guys should be yes, yes, yes, yes, yes. Let's go to our RecurringTransactions design view. FrequencyID, my default for that's going to be two. Most expenses are monthly, let's go with two.
All right, one more. Grocery shopping every other week. Grocery, $400 every two weeks. So, last time you went was 7/2, and in 14 days you have to go again - look at that, perfect.
One more thing we've got to fix: if you highlight something in here and sort it, you get this "object doesn't contain the automation object color scheme" because to get the color schemes for these guys - like we did with these guys - we need that color scheme field. We have to add ColorScheme to our query underneath, to RecurringTransactionQ and get it from the account table.
So, open this guy up, right-click, design view, add in (where are you at?) "Add Table" (right there; my computer's too long today). We're going to add in from linked tables the AccountT, and that's got the - there's the ColorScheme right there. OK, so we've got to link this guy in based on the AccountID, from AccountFromID to AccountID, right there.
Just in case, we're going to make it a left join. Bring in ColorScheme, and just make sure you've still got your new row. You should have your ColorScheme over here.
Now, with the ColorScheme in place, if we open up the RecurringTransactions, we get our colors.
As I mentioned earlier for the members, in the Extended Cut, if you've been following along with my Account Balances series, we're going to make some buttons. The buttons will allow you to mark it paid only, which means it just updates the dates right here, or you can mark it paid and add those transactions to your transaction table in the Account Balances database. That's pretty cool - that's coming up in the extended cut for the members. Silver members and up get access to all of my extended cut videos, and gold members get access to my code vault and you can download these TechHelp databases.
If you want all of this stuff - the Account Balances, the Expense Tracker, all of it built into a nice, ready to go database - I did put it together as the Account Balances and Recurring Expenses Template. You can find more information on my website. There's a QR code you can scan right there to take you right to it. It's got all of this stuff and more. There is lots of stuff I've added to it since I finished the TechHelp database, so check it out.
That is going to be your TechHelp video for today. I hope you learned something. Folks, live long and prosper. I'll see you next time.Quiz Q1. What is the primary purpose of the expense tracker described in the video?
A. To compare expenses between different years
B. To track and manage recurring expenses and their due dates
C. To generate sales reports for a business
D. To calculate employee payrolls
Q2. Which table is used to store the frequency type of recurring transactions?
A. AccountT
B. SavedTransactionT
C. FrequencyT
D. PaymentHistoryT
Q3. What is a key reason for using a Frequency table in Access instead of a simple code like 'M' or 'D' in Excel?
A. It makes the database less complex
B. It allows for more sophisticated relationships, sorting, and easier use with the DateAdd function
C. Excel cannot store codes at all
D. It is required by Microsoft Access to avoid errors
Q4. In the RecurringTransactionT table, what type of field is AccountFromID?
A. A date field showing payment due dates
B. A text field containing the account's name
C. A foreign key linking to an accounts table
D. A calculated field showing payment amounts
Q5. How is the next due date for a recurring transaction calculated in the query?
A. By simply adding 30 days to the last paid date
B. Using the DateAdd function with the interval from FrequencyT and the frequency quantity
C. By manually entering the due date for each transaction
D. By averaging all past payment dates
Q6. Why does the FrequencyT table include a SortOrder field?
A. To indicate the most important frequency
B. To allow for manual control of the display order of frequency options
C. To store the number of days in the frequency
D. For calculation of payment amounts
Q7. When would it be appropriate to make a field 'required' in the database according to the presenter?
A. Only to prevent lazy data entry
B. When the field must have a value for a record to be valid, such as DateLastPaid or FrequencyID in this case
C. Never, as required fields cause problems
D. Only for name fields
Q8. Which of the following is NOT a key field in the RecurringTransactionT table?
A. RecurringTransactionName
B. AccountFromID
C. FrequencyID
D. EmployeeID
Q9. What does the conditional formatting on the NextDueDate field accomplish?
A. It sorts the records automatically
B. It displays different colors based on how soon the expense is due
C. It prevents overdue transactions from being shown
D. It calculates the amount for each payment
Q10. Why does the presenter suggest adding an Abbreviation (ABBREV) field to the FrequencyT table?
A. To allow users to sort by abbreviations
B. To store backup data
C. To display a concise code, like 'D', 'M', or 'Y', in combo boxes for easier selection
D. To show frequency descriptions in uppercase
Q11. Why is it important to properly set tab order in forms?
A. To improve database performance
B. To ensure users move through form fields in a logical sequence
C. To change the color scheme of the form
D. To auto-generate database queries
Q12. What is the purpose of linking the AccountT table and bringing in the ColorScheme field into the recurring transaction query?
A. To calculate new account balances
B. To apply color coding to forms based on account properties
C. To store backup copies of accounts
D. To prevent duplicate entries in the database
Q13. According to the video, what is one of the benefits of building the recurring transactions in the front end of a split database and moving them later?
A. It prevents any database corruption
B. It makes it easier to develop and test before moving tables to the back end
C. It automatically optimizes the database size
D. The front end provides more storage
Q14. If a user wants to mark an expense as paid and simultaneously update the main transaction list, which feature is mentioned as coming in the extended cut?
A. A new calculation field
B. A button that both marks the record as paid and adds it to the transaction table
C. A special filter in reports
D. An automatic sorting routine
Q15. What is an example of a scenario given for using a recurring transaction with a non-default frequency?
A. Monthly mortgage payments
B. Daily loan payments
C. Annual magazine subscriptions
D. Weekly employee payroll
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-B; 8-D; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 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 focuses on building an expense tracker in Microsoft Access. In this lesson, I will guide you through the process of creating a system for tracking your recurring expenses, whether they occur weekly, biweekly, monthly, quarterly, or on any other schedule. These may include payments like groceries, utilities, car loans, mortgages, and more. We will create a database that allows you to enter the date of your last payment, specify how often the expense repeats (for example, monthly, daily, or every several weeks), and sort your expenses by when they are next due. The system will highlight overdue expenses in red, items due soon in yellow, and those that are not due yet in green, making it easy for you to see where your attention is needed.
For those following my Account Balances series, in the Extended Cut, we will integrate this expense tracker into the Account Balances database. This will allow you to mark an expense as paid and automatically add a transaction to your account's list, tying all your records together. This integration is part of why I designed the expense tracker, as it complements the work we've done in account management.
If you have not yet watched my tutorial on tracking recurring expenses in Excel, I recommend starting there so you have a solid background on the basic concepts. In that video, we list the expense name, the account from which the payment is made, the amount, the last payment date, and the frequency in terms of days, weeks, months, or years. We also calculate the next due date in Excel. Understanding this foundation will make the Access tutorial easier to follow.
For this lesson, I am building on the database from my nine-part Account Balances series. While you do not have to use that database, I will be working with the account table and some forms we previously created. Watching the series is optional but may help provide additional context. However, you should definitely review my tutorial on the DateAdd function, since we will use DateAdd in this project to determine when each recurring expense is due next.
Opening the Account Balances database, you may notice some additional features that I have added in various Extended Cuts for members, but these are not essential for this tutorial.
Although best practice with a split database is to add tables to the back end and link them, for the sake of simplicity, we will add our tables in the front end and move them later if necessary.
We already have a Saved Transactions table, which is used for transactions you might want to manually repeat. Today, we need a new table specifically for recurring transactions, such as bills or loan payments that occur on a regular schedule.
To start, we need a Recurring Transaction table. In its design, I include a primary key for each transaction, a description (named RecurringTransactionName instead of just "Name" to avoid reserved words), a foreign key to specify which account the transaction is paid from, the amount, the date it was last paid, a frequency indicator, a quantity for the frequency (for example, every two months), and a notes field for any extra details.
Unlike with the Excel version, where frequencies were denoted by simple abbreviations like "M," "D," or "Y," in Access we will use a more refined approach by creating a separate Frequency table. This helps keep our structure organized and allows for easier updates later on. The Frequency table will include a frequency ID, a sort order (so that frequencies like weekly can fall in the right order between daily and monthly), a description, a DateAdd code (used to tell Access which interval measurement to use, like "d" for days, "m" for months, "yyyy" for years), and an abbreviation for concise display in forms.
After creating these tables, we enter some sample data: perhaps a mortgage payment coming out of checking, an electric bill on a credit card, quarterly pest control, and an annual Disney pass. Assign the correct frequency to each (months, days, years), record the last payment date, and set the quantity as needed.
With this setup, we can build a query that will calculate the date each expense is next due. The query pulls the relevant data from both the Recurring Transaction and Frequency tables. Using the DateAdd function, we pass in the interval code, the frequency quantity, and the date last paid. The result tells us when the expense is next due. By default, I make key fields like frequency ID and date last paid required, since they are essential for the calculations. Although I usually advise against making fields required just to prevent incomplete data entry, in this scenario, these fields must always have a value or our logic will not function.
Once the query is complete, you can test it with additional sample data such as a quarterly recurring pest control or an annual membership fee to verify that the logic works for various scenarios.
Next, we need to create a form to manage our recurring transactions. It's efficient to copy the existing transaction form because much of the layout and logic is similar. After copying, we modify the record source and labels to fit the new table and change the color scheme so it stands out visually. The account field is linked up as before, and we add a combo box for frequency selection. Here, having an abbreviation (like "D," "M," "Y") helps to keep the combo box compact and clear. If you modify the frequency table, be sure to update the combo box to display the new data appropriately.
While developing forms, you may need to revisit your tables or queries to make small changes. This is an important part of the development process, as you often identify opportunities for improvement. For example, adding an abbreviation field to the Frequency table and then updating the combo box to use it. Such changes help to create an efficient, easy-to-use interface.
The frequency quantity field follows next on the form, allowing users to adjust how often the expense repeats (such as every two weeks or every three months). We also add a calculated "Next Due" field to the form, linking it to our query's calculation. At this point, we can set up conditional formatting so that past due dates highlight in red, upcoming ones within a week appear in yellow, and other dates remain green. This visual cue helps quickly identify which expenses need your attention.
If you need to fix default values or tab orders in your form, adjust those in the table and in the form settings so your user experience is streamlined. For example, set the most common frequency (such as monthly) as the default, and ensure the form fields follow a logical sequence when tabbing.
As a final improvement, to support color coding linked to account types (such as checking, savings, or credit), include the color scheme from your accounts table in the recurring transactions query. Link the account ID to the account table and bring in the ColorScheme field, so that each transaction form reflects the correct color for its associated account.
In the Extended Cut for members, I will show you how to add buttons to the form that allow you to mark an expense as paid. You will have two choices: update the paid date only, or update both the paid date and also add a new entry for that payment in your transaction history. This integration makes financial tracking even more seamless, combining your regular expenses with your overall account balance management.
If you prefer a ready-made solution, the Account Balances and Recurring Expenses Template, available on my website, unifies all these concepts into a polished, easy-to-use database. You can find additional information there, along with more advanced features that I have introduced since completing this tutorial.
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 Building a recurring expense tracker in Access
Creating a RecurringTransaction table
Identifying required fields for recurring transactions
Setting up a Frequency table for intervals
Configuring frequency abbreviations and date codes
Entering sample recurring expenses into tables
Calculating the next due date with a query
Using DateAdd with variable frequency and quantity
Joining tables in queries for recurring transactions
Setting required fields in tables for data integrity
Building a form for recurring transactions
Adding a combo box for frequency selection
Configuring default values in form and tables
Setting up conditional formatting for due dates
Customizing form layout and tab order
Linking ColorScheme from Account table in query
Displaying account color schemes in forms
Adding and editing sample recurring transactions
Testing frequency options with real examples |
