Multi-expense transactions and transaction types

[dougdrex]

I'm in the process of designing an Access database for my family's personal finances. Most of it is pretty simple, but I'm running into a problem wrapping my mind around how best to handle multi-expense transactions. For example, when we go to a Walmart or Target, we often spend money on groceries, clothing, toys, etc. I will be creating a query to show the total amount of each transaction (calculated field) so that I can reconcile my database balance with the bank balance.

Should I create fields in my transaction table such as Expense1, Expense2, Expense3, etc? Or is there a better way?

Also, I'm debating whether or not I should create different tables for different types of transactions. The transaction types I can think of are Deposits, Expenses (spending money at various places), and Transfers (transferring money between bank accounts).

Any and all thoughts on these topics are greatly appreciated!

[6StringJazzer]

First of all, I admire your spirit but it sounds like you are reinventing Quicken. Intuit has solved all of these problems many years ago and starts at around $40. How much is your time worth? Or you could use Mint online for free.

I am not an expert at Access but I've had some database experience. It's hard to answer this question with specifics without seeing your table schema. I would design this to have a table for Transactions with Transaction Type, date, amount, category, memo. (You could use a different table for each type of transaction but that is rather denormalized, since they all have the same attributes.) To manage multi-expense transactions, do not use multiple fields. You could include a Boolean field for Split Transaction, so if TRUE then it would refer to another table of the individual breakouts. You would have another table for these Splits, where each record has a category, amount, memo, and a foreign key to link back to the parent transaction. If you get ambitious you can make this a two-way linked list reachable from the parent, but that probably isn't necessary.

[dougdrex]

Thanks for the advice Jazzer. I'm Microsoft certified in Excel, and currently have a spreadsheet I use that accomplishes several things. It records every transaction, is used for bank account reconciliation, but also maintains a weekly and monthly budget and compares the budget against the actual amounts spent each week/month for every expense category.

I'm currently taking a class in Microsoft Access 2010, and will eventually work my way toward certification in Access as well. My real goal is to build a similar budgeting tool in Access as part of my learning experience. Perhaps I should start with an easier database and work my way toward the more complex concepts of a personal budget, bank reconciliation, etc.

Anyway, thanks for the advice. I really appreciate it!

[alansidman]

Should I create fields in my transaction table such as Expense1, Expense2, Expense3, etc? Or is there a better way?

No. That is a spreadsheet mentality and not a RDBMS mindset.

Take a look at this link on the Fundamentals of Database design.

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

You would be better served by having your table set up with

TransactionTable
-------------------
RecordID (PK)
TransDate
Vendor (FK)
ExpenseType (FK)
Amount

These would be the minimum I would suggest. The Foreign Keys would be use to link to other tables holding a list of vendors and a table holding a list of expense types.

Also, look at this link for a list of various database schema. You might find something that will be helpful.