Question Personal Finance Manager

[babaroga]

Last couple month I am working on software to track my income and expenses. However I need little bit help.

Right now I make one table for all accounts wit unique ID, and one table for all transactions.
When somebody makes transaction trough form I need now VBA code to save record and if record successfully saved update field BALANCE in tbl_Accounts depend of kind of transaction.

Let say that I have account Bank1, and my balance is 500$

When I make transaction, I need to select type: Income, Expense, and Transfer
If I make transaction 50$ and type is income I need to update my balance to 550$, if is expense my balance need to be 450$, and if is transfer I need two records, one record as prove that I spend 50$ and second that I received 50$ but on another account.

I am not sure is this model alright or maybe is much better to calculate balance based on income and outcome. But sometime I need to make correction to balance because not all transaction will be in database. So I will like just to go to account form and change number.

Thank you,

 

[Lightwave]

Hi BBG

In a simple system the way I would do this is as follows

Have your table of transactions and make sure that you reference one of the fields within the transaction table as a lookup to the accounts table such that a transaction cannot be completed without referring it to an account.

You don't quite need the double entry system like you would have in paper accounts because that is only required to give a second view of the same information. The thing about databases are they are constantly giving different views of exactly the same information. So you can filter out income / expenditure / stock so that you enter it in one place and it automatically appears in another. It doesn't automatically appear of course because it is actually the same information viewed from a different angle.

So you put in that 50$ was received in fact all transactions will probably be recorded in just the one table . Now design a form that shows all transactions but only as they relate to individual account holders. For instance say you have a receipt from your employer. You design a form such that when you click on your employer it references the table with all the receipts and expenditure and only shows you transactions which were tagged to your employer. Neither do you have to make up a new form every time you make up a new account as the form for accounts will be standardised such that the filter automatically takes its definition from the account name selected.

If you arrange it right you will still be able to edit the values within the individual accounts subsequent to the fact of data input. In fact you can change the information wherever you find the information and provided the info is held in one table it will automatically balance everything. Your bear structure is right your just thinking too much about the paper double entry thing

That's how I'd do it anyway