|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Check Register Seminar Lessons Welcome to the Check Register Seminar Total running time is 5 hours, 27 minutes. Lessons
Addendums
Updates
Database Files
Currency to English Code
Resources
QuestionsPost your questions or comments below
IntroIn this seminar series, you will learn how to build a complete check register database in Microsoft Access to manage checking, credit card, or petty cash accounts. Topics include creating a basic check register table and form, tracking cleared and reconciled transactions, printing individual and batch checks, generating payees and categories, customizing reports with pie charts and summaries, and managing multiple accounts. We will cover practical features like running balances, conditionally formatted debits and credits, custom check number functions, and detailed expense and transaction reports using both classic and modern chart objects.TranscriptWelcome to the Check Registers Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this seminar, I am going to teach you everything you need to know to track your checking accounts or other types of accounts. You can use it for credit card accounts, petty cash accounts, and so on. We will have the ability to track multiple accounts. You can print checks. I have got two types of check formats in here. We will generate some reports. We will do pie charts showing expenses by category, a full report by expenses, and a transaction report. All kinds of different stuff. This seminar actually started off as a TechHelp video. TechHelp is my video series where I try to put together a couple of videos a week and I put them for free up on YouTube. A lot of people have been asking me how to do a basic check register in Microsoft Access. Lesson one of this seminar is all the stuff that was covered in that free TechHelp video. We will build the basic check register table, and we will use a query to calculate amounts. We will determine the total, whether something is cleared or not, and if it is reconciled with the bank. We will build a simple check register form that has the debit, credit, the total, and the amount cleared. We will generate form footer totals on the bottom so you can see how much money is in your actual account versus what the bank thinks you have in your account. That is lesson one, a nice simple basic check register. As I often do, I make an extended cut for my YouTube members. Lesson two of the seminar is the same as my extended cut for my YouTube members. Lesson two will focus on running balances, how to do a running balance in your reports, and, with more difficulty, how to do one in your forms using a D-Sum function. Then lessons three through nine are all unique to this seminar. We will learn how to print checks. We will start with a single check. I will show you a function to convert currency to English, so you can type in $1,200 and 1 cent and it will write it all out like that. We will make a function to find the next check number. So if you are up to 105 and you double click that field, you get 106 next. Then we will hide the print check button if the check has already been printed and we will track that in a field. Lesson four is all about batch printing checks. The database will know which checks have check numbers that you have not printed yet. We will also calculate the amount to be printed so you know how much you have to actually send out in checks. We will then have one button, when you print the checks, to update them all and mark them all as printed. We will have select all and un-select all buttons. Lesson five is all about payees and categories. We will make a main menu form. We will create a payee table and a payee form so you can track all the people that you send payments to. We will make a category list so you can have things like utility, rent, and so on. We will put address info on checks and then we will add the actual addresses to the checks that you print. Then we will set it up so you cannot edit a check that is cleared or printed. Once it is marked cleared or once you mark it printed, you cannot edit it. In lesson six we will expand our database so we can track multiple accounts, because right now the database is just for one checking account. We will add an accounts table and put an account ID on each of our checks or each of our line items. We will make an account summary on the main menu so you can see all of your accounts, what their current balance is, and how much is cleared. Then we will change the batch printing so you can batch print each account at a time. Lesson seven has a whole ton of extra enhancements. We are really going to jazz up the check register form. We will make it so you can put in each item as a debit or a credit, but not both. It can be either a debit or a credit. Once you put one in, the other one locks itself. We will conditionally format them accordingly. The debits will show up as red, the credits will show up as green. We will add a triple state cleared filter box up top above the cleared column so you can check that on to see either items that are cleared, not cleared, or both. We will add filter by date boxes so you can put between start date and end date. We will make a little sort button above the check date, a little A to Z button. We will hide the batch print button if there are no checks to print. We will make it so you can select either a one up or three up check reports. If you have those pages, either one check on the page or three checks on the page, those are the two popular formats. The benefit of having the database is you can customize your check printouts to be whatever you want them to be. On the main menu on the summary, we will show you how many checks are waiting to be printed out of each account. We have two lessons on reporting. Lesson 8 is part 1. We will build a report menu. We will have it so you can select the account or leave it blank to show all accounts and then the dates between two dates. We will show a pie chart that shows expenses by category by dates. I will show you the classic chart object that has been in Access forever. Then I will show you the new modern chart pie chart that was added in 2016. We will go through all the properties. You can make the changes on the report form and click the refresh button and the pie chart will refresh itself. In lesson 9, part 2 of reporting, we will build two standard reports. One is an expense report. I will show you sorting and grouping levels. We will group by the category then by the payee and then show totals for each. We will do report build events where we can change the captions dynamically. Up top it will say expenses by category between two dates or expenses before this date or whatever. The account will change and so on. On the bottom of the page I will show you how to display when it was printed, what the page number is, and the total number of pages. Then we will make a basic transactions report showing all of the transactions, the check number, the description, the amount, the balance, and of course whether or not it is cleared. I am using Microsoft Access 2019. I have a Microsoft 365 subscription. Everything I cover in this seminar should be good all the way back to Access 2007 with the exception of the new modern pie chart. That is the only thing that will not work. But I do show you how to use the older pie chart object as well. I will show you how to do everything you need to do to build this database. However, if you want to learn more about what I am covering, I do recommend my beginner series as a must. Expert level one and two is very important for relationships. I strongly recommend at least Access Developer one so you have a basic understanding of how Visual Basic works in Access. If you do not understand something, post a comment in the description below the video and I will tell you what other lessons cover what you need to know. If you have questions, you will see a post a new comment or question box down below the video window. Feel free to post your questions there. If it is something that does not pertain to the class but you just have a general question about Access, feel free to post it in the Access forum. If you do not have access to the website, you can email me your questions, although I do prefer you post them online. That is all. Now sit back and get ready for lesson one. QuizQ1. What is the primary purpose of the seminar presented in the video?A. To teach how to use Microsoft Word for check printing B. To teach how to track checking and other accounts using Microsoft Access C. To explain Microsoft Excel financial templates D. To demonstrate online banking services Q2. Which of the following is NOT mentioned as a feature of the seminar's database? A. Ability to print checks in two different formats B. Generating pie charts showing expenses by category C. Calculating loan interest rates D. Generating transaction and expenses reports Q3. What is the main topic of Lesson One? A. Advanced reporting features in Access B. Basic check register creation, totals, and cleared status C. Creating a payroll management system D. Integrating Access with other Office apps Q4. Which lesson covers how to generate a running balance in reports and forms? A. Lesson 3 B. Lesson 6 C. Lesson 2 D. Lesson 8 Q5. What special function does the seminar include for writing check amounts? A. Currency to Roman numeral B. Currency to barcode converter C. Currency to English text (spelling out the amount) D. Currency to QR code generator Q6. In which lesson do you learn to restrict editing of checks once they are marked as cleared or printed? A. Lesson 1 B. Lesson 3 C. Lesson 5 D. Lesson 8 Q7. What new feature is added in Lesson 6 to the check register database? A. Multi-user input tracking B. Multiple account tracking with account summary C. Automated recurring checks D. SMS notification for low balance Q8. What conditional formatting is applied on the check register form in Lesson 7? A. Credits show in blue, debits in yellow B. Debits show in red, credits in green C. Debits show in green, credits in red D. Both credits and debits show in black Q9. What does the triple-state cleared filter box do? A. It filters entries by date only B. It lets you select only cleared items C. It allows filtering to show cleared, not cleared, or both types of items D. It sorts the register alphabetically Q10. How many check formats are available for printed checks in the seminar? A. One B. Two C. Four D. None Q11. Which reporting feature is exclusive to Access 2016 or later? A. Classic chart object B. Grouping levels in reports C. Modern pie chart D. Manual sorting Q12. What is recommended before taking this seminar if you want to fully understand the covered topics? A. Reading online banking manuals B. Completing Access Beginner, Expert, and Developer One series C. Taking a course on Microsoft Excel D. Learning SQL Server administration Q13. If you do not understand something covered in the seminar, what should you do? A. Wait for the next seminar B. Email customer support only C. Post a comment or question below the video or on the forum D. Restart the video Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-C; 10-B; 11-C; 12-B; 13-C 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. SummaryToday's video from Access Learning Zone focuses on building a complete check register database using Microsoft Access. I will be guiding you through the entire process, allowing you to track not only checking accounts but also other account types like credit cards and petty cash. This solution will let you manage multiple accounts, print checks in various formats, and generate useful reports including both transaction details and visual representations like pie charts for expense categories.This seminar originally began as a single TechHelp video, which is my free video series on YouTube where I answer common viewer questions. Many people have wanted to know how to create a basic check register in Access, so I decided to expand the topic into this much more comprehensive seminar. In the first lesson, I will cover the basics. We will design a table to hold our check register information and use queries to calculate account totals and track whether items are cleared or reconciled. I will walk you through setting up a straightforward check register form, allowing users to see debits, credits, totals, and the cleared status. We will also add form footer totals to help you understand how much money you actually have versus what the bank's records show. After the basics, the second lesson matches the extended cut that I made for my YouTube members. Here, I will show you how to work with running balances in both your reports and forms. For forms, achieving a true running balance is more complex, and we will use the DSum function to accomplish this. Moving on, lessons three through nine are exclusive to this seminar. In lesson three, we focus on printing checks. I will demonstrate how to handle a single check, including how to convert numeric currency values into written English text for the check amount, and find the next check number automatically. I will also explain how to prevent reprinting checks by hiding the print button once a check has already been printed, tracking this status in your data. Lesson four covers batch printing of checks. The system will track which checks have been assigned a number but have not yet been printed. I will guide you through calculating the batch printing amount and updating the printed status for multiple checks at once, including select all and unselect all controls. Lesson five introduces payee and category management. We will build a main menu, a payee table and form to manage payees, and a category list to classifying spending (e.g., utilities, rent). Address handling will be covered so that addresses can appear on your printed checks, and then, for checks that are cleared or printed, editing will be disabled to preserve data integrity. In lesson six, we will expand the system to support multiple accounts. We will add an accounts table and connect each check or transaction line to a specific account. The main menu will display summaries for all accounts, showing balances and cleared amounts, and batch printing will be adjusted to handle account selection. Lesson seven is dedicated to a suite of enhancements. We will refine the check register form so that each entry can be a debit or a credit, locking one field when the other is used. Debits will display in red and credits in green using conditional formatting. We will add a triple-state checkbox so you can filter for cleared, uncleared, or all transactions, as well as date range filters and sort buttons for easier navigation. We will also update batch print controls to only show when needed. Both single-check and three-check printing layouts will be included, giving you flexibility to customize check layouts as needed. On the summary screen, we will indicate how many checks from each account are waiting to be printed. For reporting, lessons eight and nine build comprehensive report tools. In part one, we construct a report menu where you can select which account to view and set the date range. I will show you how to build a pie chart summarizing expenses by category, utilizing both the classic and the new modern chart objects available in Access. You will learn to refresh the reports dynamically after changing criteria. Lesson nine will round out reporting by creating standard reports for expenses and transactions. I will show you how to use Access's sorting and grouping features to organize data by category and payee, along with dynamic captions that reflect your report criteria. At the footer, you will be able to see print dates, page numbers, and totals. We will also create a simple transaction report listing all activity, including check numbers, descriptions, amounts, running balance, and cleared status. Throughout this seminar, I am using Microsoft Access 2019 with a Microsoft 365 subscription, but most of these concepts will work going back to Access 2007, except for the newer modern pie chart feature. However, I will also show you how to use the older chart tools as an alternative. I will walk you through each step in constructing this database, but if you want to better understand foundational concepts, I recommend watching my Access Beginner series. Expert levels one and two are especially important for understanding relationships, and you should also consider Access Developer one if you are not already familiar with Visual Basic for Applications in Access. If you find anything unclear or have questions, I encourage you to leave a comment below the video. I also recommend using the Access forum for general questions, and, if necessary, you can email me though posting online will often get you a quicker response. Now, get ready to start with lesson one. If you want detailed, step-by-step video instructions for everything covered here, you can find the full seminar on my website at the link below. Live long and prosper, my friends. Topic ListBuilding a basic check register table in AccessUsing queries to calculate account amounts Tracking cleared and reconciled transactions Creating a check register form with debit and credit fields Generating form footer totals for balances Implementing running balances in reports Using DSum to create running balances in forms Printing individual checks from Access Converting currency amounts to English text Automatically finding the next check number Hiding print check button for printed checks Batch printing multiple checks at once Marking checks as printed after batch printing Calculating total batch printing amounts Adding select all and unselect all for batch printing Creating payee and category tables Designing a payee management form Adding address information to printed checks Restricting edits on cleared or printed checks Expanding for multiple account management Adding an accounts table and account IDs to checks Building an account summary on the main menu Batch printing checks per specific account Customizing the check register form for debits and credits Conditional formatting for debit and credit fields Building a triple state cleared filter Adding date-range filtering to the register Implementing a check date sort button Hiding batch print button when inactive Selecting one-up or three-up check reports Displaying pending printed checks per account Building a reports menu Filtering reports by account and date range Creating pie charts for expenses by category Comparing classic and modern chart objects in Access Refreshing pie charts dynamically on report forms Building expense reports with sorting and grouping Customizing report captions dynamically Displaying print date, page number, and total pages in reports Building a transactions report with balances and statuses |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access check register seminar lessons PermaLink How To Build a Complete Check Register With Multiple Accounts, Printing, Reports, and Charts in Microsoft Access |
