Results 1 to 12 of 12
  1. #1
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9

    Question Table design for multi bank account budget app.

    Hi Access people! Years ago, I created a simple budget app in Access that has served me well over the years. However, there a lot of things I would like to differently, so I have decided to create a new one from scratch. When I created the first budgeting app, I only had the one checking account and no other types. However, now, I have several accounts (Checking, Savings, Credit Card, etc.) and I would like to be able to do simple things such as post payments from the accounts and have them applied to correct categories and sub-categories. This was simple enough when I was only using one bank account. But, I am having a little trouble wrapping my head around the table design when posting deposits and payments from multiple accounts.

    I am "thinking" something like this "might" be okay:

    tblBanks
    BankID pk
    BankName text

    tblCategories
    CatID pk
    CatName text

    tblSubCats
    SubCatID pk
    SubCatName text
    CatName fk

    tblTransactions
    TransID pk


    BankName fk
    DebitAmount Currency
    CreditAmound Currency
    Cat fk
    SubCat fk
    Description memo

    Does this look okay? Or, am I looking at this all wrong? I am kind of struggling how using the multiple accounts will work when trying to create forms or reports for viewing balances by account, category, subcat, etc. Any guidance some of you pros could offer would be greatly appreciated. Thanks in advance for reviewing my post.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Don't think you are far out on the basis you are only tracking payments and same account receipts (such as interest on a saving account). Not so ideal if you need to track transfers between accounts

    Suggest ensure your naming is consistent - BankName and CatName is text in one table and numeric in another for example. And you don't appear to have a table for bank accounts unless that is tblBanks?

    Description is a reserved word, suggest change to say TrtanDescription - also doubt it needs to be memo, would have though 255 chars was enough

    I would include a transactionDate field in tblTransactions

    you don't really need two tables for categories - suggest keep your subcategories table which can look back on itself - a category would be identified by having a null value in the CatName field

    Personally, and this is just a my preference, I prefer to have one amount field using positive and negative values to represent dr and cr values (or the other way round if you prefer)

    However, if you need to track transfers then I suggest your transaction table need to look more like this

    tblTransactions
    TransID pk
    DrBankID fk
    CrBankID fk
    Amount Currency
    Cat fk
    SubCat fk
    Description memo

    And continuing down this route and you can have the situation where a transaction is split - example might be you pay an invoice which needs to be split across multiple categories. Then you'll need a 'splits' table as well.

  3. #3
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Quote Originally Posted by Ajax View Post




    Personally, and this is just a my preference, I prefer to have one amount field using positive and negative values to represent dr and cr values (or the other way round if you prefer)

    However, if you need to track transfers then I suggest your transaction table need to look more like this

    tblTransactions
    TransID pk
    DrBankID fk
    CrBankID fk
    Amount Currency
    Cat fk
    SubCat fk
    Description memo

    And continuing down this route and you can have the situation where a transaction is split - example might be you pay an invoice which needs to be split across multiple categories. Then you'll need a 'splits' table as well.
    Excellent ideas. I have not started creating the tables yet. So, I will definitely taking your naming convention suggestions in to account. Your suggestion for a trans date is most welcome and something i just totally spaced.

    The transfers between accounts is something else I just spaced and overlooked. That will surely be needed at some points. As far as splitting transactions, it would be nice but not something I would probably use a lot. Besides, not really sure how a "splits" table would look or how I would integrate it into the DB.

    Your suggestion for using a single "Amount" field is interesting. But, it leaves me wondering how I would go about posting the transactions to the individual bank accounts table (which I totally left out of my first post.) I am assuming that the DrBankID and CrBankID foreign keys are coming from the same table. If so, how would I go about updating both account records simultaneously? Debit an amount from one account and credit it to the other, I mean.

    You kind of lost me with the "null" value suggestion for the category field. If I can get away with using a single table, that would be ideal. But how can the subcats table "look back on itself?" If you could clarify a little, it would be greatly appreciated.

    Once again, thanks a ton for your suggestions. I will certainly be incorporating a lot them.

    Cheers

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you said

    You kind of lost me with the "null" value suggestion for the category field
    tblCategories
    CategoryPK autonumber
    CategoryName text
    CategoryFK long - links back to categoryPK which will be the primary category, if left blank (null), then it is a primary category

    so a query like this will give you all your categories

    Code:
    SELECT *
    FROM blCategories P INNER JOIN blCategories S ON P.CategoryPK=S.CategoryFK
    WHERE P.CategoryFK is null

    I am assuming that the DrBankID and CrBankID foreign keys are coming from the same table. If so, how would I go about updating both account records simultaneously?
    you don't need to - it is the same record - amount would always be positive in this case so the accounts in the Cr field would multiply that value by -1 when adding up. Not sure what you mean by updating both account records - hope you don't mean you want the account tables to be updated with current balance - that is a no no. You don't need to - it is the same record - amount would always be positive in this case so the accounts in the Cr field would multiply that value by -1 when summing.

  5. #5
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Quote Originally Posted by Ajax View Post
    you said

    CategoryPK autonumber
    CategoryName text
    CategoryFK long - links back to categoryPK which will be the primary category, if left blank (null), then it is a primary category

    so a query like this will give you all your categories

    Code:
    SELECT *
    FROM blCategories P INNER JOIN blCategories S ON P.CategoryPK=S.CategoryFK
    WHERE P.CategoryFK is null
    Okay, thanks a bunch for this. Still trying to get the "linking back" part, though. Sorry if I am being dense. So, would I just create a look up field for the CategoryFK field and link it to the ID number of the CategoryIDPK? Or would it be better to create this relationship manually, while leaving the CategoryFK field as Number/ Long Integer?


    Quote Originally Posted by Ajax View Post
    you said

    you don't need to - it is the same record - amount would always be positive in this case so the accounts in the Cr field would multiply that value by -1 when adding up. Not sure what you mean by updating both account records - hope you don't mean you want the account tables to be updated with current balance - that is a no no. You don't need to - it is the same record - amount would always be positive in this case so the accounts in the Cr field would multiply that value by -1 when summing.
    Okay, understood. I think I wasn't clear with my question. Of course, I don't want to keep the balance as a field in the bank/accounts table. I guess is what I am wondering is how the amount entered into the transaction would be reflected in the Debit and Credit accounts? Or, would I just need to create a query to search the transaction table for the appropriate values when create forms and reports? Hope this make sense?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So, would I just create a look up field for the CategoryFK field and link it to the ID number of the CategoryIDPK?
    strongly recommend you don't use lookups in tables - just use a combo in a form. but you have the principle correct. Here's why not you use lookups in tables http://access.mvps.org/access/lookupfields.htm. Just because you can, does not make them a good idea

  7. #7
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Okay. I think I am getting there, thanks to your help that is. So, what do you think of the following layout?

    Click image for larger version. 

Name:	Access_Relationships.png 
Views:	15 
Size:	59.4 KB 
ID:	41019

  8. #8
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Also, how can I link the Category_FK back to the Category_ID PK? I can seem to find a way to do it the relationships window. Thanks again.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks OK, you have some typo's and illegal characters in the field names - you've dropped tranDescription for some reason and not sure what 'banknotes' is all about. Also not sure why you need currencies? if you do, presumably you will also need exchange rates in the transactions table to be able to convert back to a common currency. How you might do this really depends on what your app is required to do

    how can I link the Category_FK back to the Category_ID PK?
    you don't need the subcategories table - just do the following

    1. drag the tblCategories onto the relationships grid again - it will have a _1 suffix
    2. create a join between CategoryFK in the original table to the categoryPK in the _1 suffixed table
    3. modify your current join from transactions subcategoryFK to also join to tblCategories
    4. you don't really need the categoriesDK in transactions since you can get to it from the join in 2 - but it might be useful if you want to limit your subcategories first in your combo and you are using a continuous form.


    your tblCategories table would look something like this

    PK....Category...FK
    1......Expenses..Null
    2......Hotel.......1
    3......Meals.......1
    4......Travel.......Null
    5......Train........4
    6......Flight.......4
    etc

    those with a null FK are the primary categories, those where FK is populated are subcategories. Note also you cannot enforce referential integrity on this join because of the nulls.

  10. #10
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Quote Originally Posted by Ajax View Post
    looks OK, you have some typo's and illegal characters in the field names - you've dropped tranDescription for some reason and not sure what 'banknotes' is all about. Also not sure why you need currencies? if you do, presumably you will also need exchange rates in the transactions table to be able to convert back to a common currency. How you might do this really depends on what your app is required to do

    you don't need the subcategories table - just do the following
    Okay thanks. yeah i corrected the typo and got rid of the illegal character. thanks for catching that for me.

    Trans_Description was there, but was just down below the scroll area. Resized it to make it visible.

    Got rid of the SubCategories table.

    Banknotes is just a memo field so I can enter contact notes for different people at the bank and so on.

    Yeah I need multiple currencies. I reside overseas and most of my income comes from the US. I have a few US accounts and then ones in the country where I live. I don't, however, pay any of my bills with USD. I just transfer them via a service to my accounts in the country where I live. So, I can just deduct X amount of dollars from the US account and add X amount of the foreign currency to my local account.

    If I need to view the USD account balances in the local currency, I can do that with an external Excel sheet I have that updates the exchange rate every few hours. I have some VBA code saved that will let me update the exchange rate in Access on startup from the Excel file. It's not the most elegant solution for currency updates, but it works. And besides, I have no idea how to get the exchange rates pulled from the web into Access directly. However, like I said, I did figure out how to do it in Excel. So, until I can find a better solution, I just use an unbound field on a form to pull that rate from the linked Excel sheet.

    I suppose it would be nice to be able to use the exchange rate for transfers from USD accounts to local currency accounts. But, there almost no way the rate would sync with what I receive when I actually do the transfers. So, for the time being at least, entering separate transactions for the withdrawal and deposits seems to be the most effective route. Of course using a transfer function is still useful when moving money between accounts that use the same currency.

    Anyway, here is the updated relationships window. I am debating if I want to add a vendors table or not. I am not sure. Might just add more complexity and bloat than is needed. Since, I already know who I am paying, and for what, 99% of the time.

    Thank you so very much for all your help. I appreciate it more than you know. Of course, further suggestions or observations are welcomed and appreciated as well.

    Click image for larger version. 

Name:	Budget_Relationships.png 
Views:	14 
Size:	61.0 KB 
ID:	41021

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But, there almost no way the rate would sync with what I receive when I actually do the transfers
    agreed - plus you often have a fee as well
    Might just add more complexity and bloat than is needed.
    a bit of complexity, but not much bloat!

    what you can do on your form for the description field is use a combo box with a rowsource along the lines of

    Code:
    SELECT DISTINCT tran_Description
    FROM tbl_Transactions
    ORDER BY tran_Description
    and set limit to list to No

    then once you have typed 'electricity' or whatever, it will appear in your combo next time so you can just select it after entering a character or 2.

    you will also need to requery the combo in the form current event you need to update the list for the next record in the same session.

  12. #12
    jessdaddy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Kuala Lumpur
    Posts
    9
    Thank you so very much, Ajax. Your help has been invaluable.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Effective Budget Design
    By StatsAnalyst in forum Database Design
    Replies: 3
    Last Post: 07-21-2016, 03:47 PM
  2. Replies: 5
    Last Post: 11-12-2015, 05:25 AM
  3. Replies: 7
    Last Post: 01-11-2015, 09:45 PM
  4. Calculate bank account balance
    By dougdrex in forum Queries
    Replies: 8
    Last Post: 11-28-2014, 01:59 PM
  5. Bank account catagories
    By broecher in forum Database Design
    Replies: 1
    Last Post: 10-16-2010, 10:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums