Calculating columns total

[skkakkar]

Hi,
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.
Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
Look forward to guidance for solving the above objective.

 

[MarkK]

That's how you make a spreadsheet, not a database. In a database you would have two tables, one for expense, and one for expense type, and most of your fields in your pers_exp tables should be rows in tExpenseType. Tables should look like . . .

tExpense
ExpenseID (Primary Key)
ExpenseTypeID (Foreign Key)
Date
Amount (postive or negative)

tExpenseType
ExpenseTypeID (PK)
TypeName

So now for each single expense you add, you add a new row in tExpense, and you link to the type using the foreign key. This way you can add types without modifying your tables.
hth

 

[skkakkar]

Hi Markk,

I feel you have guided me in right direction. I will work it out and inform you.
I profusely thank you for the same.

 

[skkakkar]

Hi,
I have reworked the personal expenditure data base on the basis of guidance provided by Markk. It is working fine and provides me expenditure totals date-wise as well as type wise.(File Attached) However it has scrapped my entry forms which are to be redesigned with following objective.

a) it should enter date, amount, type of expenditure ID
b) It should have flexibility to choose type of expenditure ID from a combo box or other means.
c: There are 3 table exp_amount, exp_type, total_exp_details(with date as primary ID)
d) All these table have to maintain referential integrity.
e) Entry form has to have a system to maintain referential integrity as new dates and new expenditure type are added preferably automatically.

I look forward to some pointers like appropriate thread, site reference which can help me in providing guidance in the right direction. On my own it may be hit and miss type of approach but forum experts can show me the right path to go rest I will do all the laborious work.

 

[skkakkar]

Hi,
Further to my previous post today I have created one main form and two sub-form which are enabling me to proceed further and allow data entry. (File attached) However If I wish to enter a new expenditure type ,it restrains me from adding mentioning that it has a related item in other form/ table. For existing date and expenditure types it works okay.
I am missing something somewhere and look forward for guidance to improve it further.

 

[MarkK]

You should make your primary keys Long Integer AutoNumbers. Your related foreign keys should also be Number fields containing Long Integers. This will provide you with the most efficient links.

Then you need to think more about your tables, for instance, lets say you go to the supermarket and you buy Fruits and Vegetables and Milk, but you only pay once, right? So there is one transaction with many types in it. And you have to adjust the account you paid with either bank or cash or whatever.

So you need to record a Transaction, which has a Date and a Payee. Then, these other things like Cash, Bank, Milk, Hygiene (what you call types) are more accurately called Accounts, so in one Transaction you might make many Posts to various Accounts, so those are tables you will need.

tTransaction
TransactionID (PK)
Date (don't put the years in your table names. The year is in the date)
Payee

tAccount
AccountID (PK)
Type (asset, liability, expense, revenue)
Name

tPost
PostID (PK)
TransactionID (FK)
AccountID (FK)
Notes
Amount

So one Transaction can have Posts to many Accounts, and post amounts in any one Transaction must balance to zero. This makes it very simple to find the balance of your accounts. This is the basic structure of an accounting system.

This also describes a Many-To-Many relationship between Transaction and Account, with Post serving as the junction. In this way the Value of a post can belong to both the Transaction AND the Account.

hth

 

[skkakkar]

Hi Markk
I find your suggestions quite valuable and practical. I will try to modify the system as soon as possible incorporating the important features mentioned by you.
Your guidance is clear to me for tTransaction and tAccount but tPost portion is not very clear to me. Kindly guide for one sample entry fields taking cue from my database.
Many Many Thanks

 

[MarkK]

When we pay for something, money comes from somewhere, and goes somewhere else. At a minimum then, one transaction always affects a minimum of two accounts, which must balance. If I make a Visa payment for instance, I transfer money from my Chequing account, say, and to my Visa account. So I credit one account and debit the other. So that one Transaction has two Posts. The each post has a value, and belongs to both a Transaction, and an Account. And, that Transaction must balance to zero, so all the credits must equal all the debits.

That is a simple example, but at a supermarket, I might pay with a gift card and some cash, and the rest with a debit card. I might buy fruits, vegetables, hygiene products, cleaning products, clothing, housewares, all things that I might wish to allocate to different expense accounts. So I might have a single Transaction that must allocate value to many Accounts. But an "Account" doesn't have a value. Neither does a Transaction. We need another "thing" or "object" to carry the value. That object is a Post. It belongs to an Account AND a Transaction, AND it carries a value.

Is that making sense?

 

[MarkK]

A Post is like a TransactionDetail. A Transaction might contain any number of amounts that need to be debited and credited to and from different Accounts. Maybe TransactionDetail is a better word to use, not Post. But this object that contains the amount is not an Account, and it is not a Transaction. It is a different type of thing, and it has the property of belonging to a Transaction AND Belonging to an Account at the same time.

 

[skkakkar]

Hi Markk,

Thank you very much. You have clearly explained. It gives me enough lead to proceed further.

 

[skkakkar]

Hi Markk,

Greetings. I have modified the personal accounts database and have created 3 tables tTransaction, tAccount, tLedger. Also created forms and sub-forms.
Summation query "exp_summ_acttws" return 0 amount means sources and utilization are matching. (New file attached) I am very much thankful to you for bringing me this far.
I am not very clear as how I should proceed further while making new enteries.
a) Whether the relationship of tables is ok.
b) For any new transaction involving new Account ID how should enteries be proceeded so that referential integrity is maintained. Present set of forms seems to be inadequate for this purpose.

 

[MarkK]

Here is the direction I would go with this.

If you open the fTransaction form, note that there should always be at least two ledger entries in a transaction that will balance. If you look at my example entry for Safeway, there are two forms of payment and many expense categories, but still the transaction posts (ledgers) balance to zero.

Hope this makes sense to you.

 

[skkakkar]

Hi Markk,

You have nicely explained the concept with illustrated example. I will try to modify database accordingly and shall aprise you after the needful is done. Heartfelt Thanks to you.

 

[MarkK]

You bet skkakkar, all the best!

 

[skkakkar]

Hi Markk,

After studying example entries provided in a sample database, I have reworked the personal account ledger and is being attached for your kind perusal. I hope it is in order.
I would like to know that how the Balance column in fTransaction form is getting its balance value. I am not getting any clue from field properties.
Once again thanking you for your kind indulgence and excellent guidance enabling me to make this database for personal use.

 

[MarkK]

You are using the Payee field incorrectly, I think. I would use that field to record the name of the business or person with whom the transaction occurred. If I buy groceries there is a store near here called "Save-On-Foods." Then that is what I put in Payee, not my method of payment. That is handled by your tLedger entries. If you lend money to your Mom, you put "Mom" in the payee field. And so on.

There is VBA code behind the forms. Hit <Alt>+<F11> and the VBA IDE opens. Show the project explorer (if it isn't already visible) using <Ctrl>+<R>. Double-click the form names in the tree, and you'll see the code behind each of the forms. Forms and Controls raise various events which can be used as triggers to perform specific actions. In this way, you can place a button of a form, and run code when the user clicks the button. In our case, we have no buttons, but we run code when a Form_Load(s), or when a record becomes current (Form_Current()). We also run code in a couple of places using Double-Click.

 

[skkakkar]

Hi Markk,

Your observation about payee field is understood and appreciated. It shall be incorporated immediately.
I am grateful to you for explaining the working and design aspects of the forms.
I feel that this problem stands resolved and if you agree this thread can be closed.

Best Regards
skkakkar

 

[MarkK]

You take care. It has been a pleasure working on this with you.