Transaction Tables
By Richard Rost
4 years ago
3 Methods for Creating Tables to Track Transactions
In this Microsoft Access tutorial, I'm going to show you my three different methods for tracking transactions: using a single field, using two fields, and using a single field with a transaction type field.
Ian from Columbus, Ohio (Platinum Member) asks: I'm designing a database to track my bank transactions. Should I use separate tables for deposits and withdrawals?
Members
Members will learn how to setup the third, most advanced transaction table with the transaction types.
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!
Pre-Requisites
Links
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 transaction tables, Create a Transaction Table, transaction processing, Managing transactions
Intro In this video, we will talk about three different methods for designing transaction tables to track bank transactions in Microsoft Access. I will compare using a single field with positive and negative values, separate fields for deposits and withdrawals, and a more advanced method with transaction types, and discuss the pros and cons of each approach depending on your experience level and your users' needs, using practical examples to illustrate each method.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you my three methods for creating tables to track transactions in Microsoft Access. And just to be clear, I'm talking about bank transactions here, not SQL transactions. That's a totally different video.
Today's question comes from Ian in Columbus, Ohio, one of my platinum members. Ian says, "I'm designing a database to track my bank transactions. Should I use separate fields for deposits and withdrawals or one field with positive and negative values?"
Ian, I get asked this question all the time.
I've pretty much got three different methods that I'll use depending on the type of database I'm building and who I'm teaching. I've got different methods for beginner, intermediate, and advanced level students.
It's funny because Ian sent me this question about a week or two ago and this just came up in the MS Access subreddit. Lots of people posted some pretty good ideas and some information in here. Of course, I chimed in. I'll put a link to this down below in the description so you can go and read all this if you want to.
Basically, I've got three different choices that I recommend depending on who you are and who you're building the database for.
First, you have the single field. You put your transactions all in one field, call it amount or whatever you want to call it. Credits will be positive values. Debits will be negative values. This is the easiest to build for a beginner developer, someone who's just learning Microsoft Access. But it's harder to use because as a user, you have to remember to put your checks in as negative numbers and your deposits in as positive numbers. That can actually get more confusing for the user. So, what I'll do in that case is usually use a little conditional formatting on that field to make it red or green depending. That's not too hard to teach beginners.
Then you've got the two field situation where you've got a deposits and a withdrawals field. It's a little harder to build because you need some query logic in there to do the math right. But it's much, much easier to use. I recommend this for most of my intermediate level students.
It's not that hard to build. It's much, much easier to use from a user standpoint. This is actually the method that I recommend and use in my check register database and my template. If you want to watch this video, this is a free video on my website. I'll put a link down below. You can go check this one out as well.
And then the third method is actually the most advanced but gives you the most flexibility. All values are positive. You enter them into one field. You're back to a single field again, but you have a transaction type identifier that can tell you what kind of transaction that is. It's the hardest to build from a developer standpoint, but I think it's the easiest to use from a user standpoint because you have one field. You pick the type of transaction, and the database then knows whether it should be positive or negative based on the transaction type.
It allows additional flexibility because you can have multiple transaction types. You can separate your withdrawals and deposits from transfers. Transfer in, transfer out is different. You can do bank fees as a separate type of transaction. So it gives you a lot more flexibility if you're building a more full-fledged accounting system.
Let me show you some examples.
Here I have three examples to show you, and of course the check register template, which I built in the other video. Let me give you a quick look at that in a minute. But let's take a look at database one. Real simple to build, you've got a transaction table. I've scaled this way down - transaction ID, your date (don't make a field called just 'date', that's a reserved word), your description, and the amount. Notice that positive values are credits and negative values have to be your debits. Your transaction form is nice and easy. I put a little bit of conditional formatting in here to help the user, but you have to remember to type them in that method.
Database two:
We've got our transaction table - same basic thing, but instead of one field, we have two: one for debit, one for credit. When the user goes to enter these things in, they can very clearly see this whole column is red, put your debits here, put your credits there. The computer can figure out which is which. This is a relatively straightforward and easy calculation to do with a query.
In design view, here's my total field - it's a simple mathematical equation. The total is the credit plus multiplying the debit by negative one to make it negative. Very easy, very simple, easy to build, easy to use. This is why I prefer this method for most users. Easy to build, doesn't require a lot of knowledge from a developer standpoint, and users can pretty much figure out how to use that.
This is how I built my check register database. Pretty straightforward, easy to develop, easy to modify from a developer standpoint, and relatively easy for users to work with.
Now, my advanced method, database three, requires a transaction type table. You have deposits, withdrawals, transfers in, transfers out, and then you specify whether that's a debit or not. In your transaction type table, you do this - you put a transaction type ID so that the database knows which type of transaction it is.
Then the transaction form looks like this. You come in here and you put in, you know, I just bought some trouble bait, and that's a withdrawal, and it was $13.
This one's a little more difficult to develop from a developer standpoint. You have to know relationships between your tables, a little more complicated of a query function. So I usually save this one for advanced users. Even so, for most people like if you're doing personal finance or a basic check register, you don't really need different accounts here. A debit and the credits are usually enough for most small businesses or home finance. But if you start getting into more advanced accounting systems, you might need to be able to specify the type of transaction.
I've scaled these way down for the examples for the video here. Usually you'll have a check number field and a vendor ID and all kinds of other stuff on these check registers and transaction tables, but I scaled this down for the video.
That's pretty much my three choices. Are there other methods? Absolutely. It completely depends on your business, what works best for you, what your needs are, and what your end users look like.
If you're dealing with people who are pretty computer savvy, go with something that's a little more advanced for them. In fact, someone in the subreddit even asked if they should use multiple tables. Personally, I don't think you should use multiple tables for the same types of transactions. I think all transactions should be in one table. But if you've got a compelling business need to use two tables, that's great. I find it's easier to have everything on one table than it is to try to put together data from two tables with a union query later. Again, that's my opinion. Feel free to post your comments below. Let me know what you think. What kind of accounting system do you use? Do you use multiple tables? Let me know.
So that's pretty much it for this TechHelp.
Members, I haven't forgotten about you. In the extended cut for the members, I'm going to walk you through that advanced table method. I'll show you how the transaction types work and what the equations are in the query. We'll go over some of that stuff.
And of course, gold members, you can download all three of these databases and check them out for yourself.
That's it for today's TechHelp. I hope you learned something. See you next time.Quiz Q1. What is the main topic discussed in the video?
A. Methods for tracking SQL transactions in Microsoft Access
B. Best practices for splitting databases in Access
C. Methods for creating tables to track bank transactions in Microsoft Access
D. Setting up permissions in Access databases
Q2. What is the easiest method to build for beginner developers when tracking transactions?
A. Using multiple tables for each transaction type
B. Two fields: deposit and withdrawal
C. Single field for amount with positive and negative values
D. Separate database for credits and debits
Q3. What is one downside of the single field method for users?
A. It requires multiple tables to track transactions
B. Users have to remember to enter debits as negative and credits as positive
C. It is harder to calculate totals
D. It does not work with conditional formatting
Q4. What is recommended for intermediate users in tracking bank transactions?
A. Single field method
B. Three fields: debit, credit, and transfer
C. Two fields: deposit and withdrawal
D. A spreadsheet outside of Access
Q5. What is a benefit of the two field method for users?
A. It is easier for beginners to set up
B. Queries become unnecessary
C. Users can clearly distinguish between debits and credits
D. It provides more flexibility for advanced accounting systems
Q6. Which method allows for the greatest flexibility with transaction types?
A. Single field with positive and negative values
B. Two separate transaction tables
C. Single field plus transaction type identifier
D. Only using debits
Q7. Why might you use a transaction type table in the advanced method?
A. To store only deposit records separately
B. To allow for multiple transaction types (deposit, withdrawal, transfer, fees, etc.)
C. To eliminate the need for queries
D. To automatically generate reports
Q8. What is one challenge of the advanced account tracking method for developers?
A. You cannot add more fields later
B. Managing relationships and queries becomes more complicated
C. Conditional formatting does not work
D. It is less flexible for future needs
Q9. What does Richard Rost recommend about storing all bank transactions?
A. Use multiple tables, one for each transaction type
B. Store all transactions in one table unless there is a compelling business need
C. Split transactions into as many tables as possible
D. Use only spreadsheets for transaction data
Q10. Which users would benefit most from the advanced method with transaction type tables?
A. Complete beginners
B. Users with no experience in Access
C. Advanced users or those building full accounting systems
D. Users entering very few transactions per year
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-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.Summary Today's video from Access Learning Zone covers three different methods I recommend for building tables to track bank transactions in Microsoft Access. This question often comes up, usually phrased as whether you should design your database with separate fields for deposits and withdrawals, or use a single field that holds both positive and negative values. Let me walk you through my preferred approaches and when each is most appropriate.
I generally use one of three methods, depending on the complexity of the database you need and your experience level with Access.
The first method is the simplest: using a single field for the transaction amount. Credits are represented by positive numbers and debits by negative numbers. This is easy for a new developer to set up, but it can be confusing or error-prone for the user because you have to remember to put checks in as a negative number and deposits as positive. To help with usability, I typically add conditional formatting to visually differentiate between credits and debits, like making them different colors. This helps, but there is still a learning curve for users.
The second approach uses two fields – one for deposits and one for withdrawals. While this adds a little complexity from the developer's perspective because you need to write a query that performs the correct calculation, it makes things much easier for the user. They simply enter numbers in the appropriate column, and the query calculates the correct running total, usually by adding credits and subtracting debits as needed. This is my preferred method for most applications, and it's actually what I use in my own check register database template. This method strikes a nice balance between ease of use for the user and relative simplicity in development.
The third method is the most advanced, and it provides the greatest flexibility. Here, you enter only positive amounts into a single field, but you include a transaction type field to specify what kind of transaction it is, such as deposit, withdrawal, transfer in, or transfer out. The database then determines how to treat that transaction based on its type. This method supports much more complex accounting situations, since you can distinguish transfers from debits and credits, and even create different types of fees if you need to. However, building this database requires more knowledge about table relationships and query logic. I tend to reserve this style for advanced users or more sophisticated business needs.
To help you visualize, imagine three sample transaction tables. The first and simplest has just the fields for ID, date, description, and amount; the second has separate fields for debit and credit; the third incorporates a transaction type lookup, allowing for multiple transaction types with only positive values recorded in the amount field.
When presenting these samples, I keep the examples simple. For a real-world application, you might add fields like check number, vendor ID, or others, but the general structure stays the same.
There are certainly other methods, and the right choice depends heavily on your business needs and your end users. In most cases, I keep all transactions in one table, which makes things simpler, rather than splitting similar transactions across multiple tables. However, if you have specific requirements, do what works best for you.
For members, I have not overlooked you. In the Extended Cut for members, I go deeper into the advanced table method, demonstrating how the transaction types connect and how the necessary queries work. Gold members can also download copies of all three sample databases to try out for themselves.
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 Single field method for transaction amounts
Conditional formatting for transaction fields
Two field method for deposits and withdrawals
Creating queries to calculate transaction totals
Advantages and disadvantages of each method
Designing a simple transaction table
Advanced method with transaction type table
Using transaction type IDs for flexibility
Choosing the best design based on user skill level |
