If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Suggestions to Split Transaction details
I have a simple database for keeping my checkbook (or all finances)
straightened out. and i am looking for suggestions on how to make my deposits splittable.. i have two simple tables T_Transactions TransactionID TransactionDate TransactionAmount TransactionDescription ExpenseCategoryID And T_ExpenseCategory CategoryID CategoryName IsTaxable when i enter a deposit the bank statement only shows one amount but it may actually be the sum of 6 checks all needing different categories assigned to them.. Does anybody have any neat ideas about how to setup a database that can do this. I know the basics pretty good but im not sure if i can figure out a good user interface for this.. or how to maintain good normalization if one transaction has no split details while others have many split details.. I remember years ago using quickbooks and there was a split button that sent you off somewhere. Any ideas would be greatly appreciated.. Thanks Barry |
#2
|
|||
|
|||
Suggestions to Split Transaction details
Barry
While I admire your determination, I also wonder why you wish to recreate the wheel? If Quicken or QuickBooks can already handle "splits", why do you need to figure out a way to do that? Also, your opening phrase "a simple database" seems somewhat in conflict with the idea of incorporating a mechanism for "splits". (but if you are as determined as you seem to be, consider: One transaction will have one (or more) "splits". I'm defining a split as a portion of a check used for a different purpose, not a totally different check. This implies that you need a table to hold splits. That table might look something like: trelSplits SplitID TransactionID SplitAmt SplitDescription SplitCategoryID and you'd need to modify your Transaction table to include: tblTransaction TransactionID TransactionDate TransactionDescription You would not need to put the transaction total in, as it would (and should) be calculated as the sum of the splits. Note that this design basically requires EVERY transaction to have (at least) one split. Best of luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Barry A&P" wrote in message news I have a simple database for keeping my checkbook (or all finances) straightened out. and i am looking for suggestions on how to make my deposits splittable.. i have two simple tables T_Transactions TransactionID TransactionDate TransactionAmount TransactionDescription ExpenseCategoryID And T_ExpenseCategory CategoryID CategoryName IsTaxable when i enter a deposit the bank statement only shows one amount but it may actually be the sum of 6 checks all needing different categories assigned to them.. Does anybody have any neat ideas about how to setup a database that can do this. I know the basics pretty good but im not sure if i can figure out a good user interface for this.. or how to maintain good normalization if one transaction has no split details while others have many split details.. I remember years ago using quickbooks and there was a split button that sent you off somewhere. Any ideas would be greatly appreciated.. Thanks Barry |
#3
|
|||
|
|||
Suggestions to Split Transaction details
Jeff
Thanks for your response In regards to your "wish" I am beginning to become a Control Freak!!! and i am enjoying access so much because it lets me do what I want. Also Quickbooks seems to want to run my life. Steal my internet connection. and tie up any available processor space i have left. As for your response i guess what i needed but was afraid to hear was "EVERY transaction has to have (at least) one split." wich is what i expected but am not sure quite what a nice interface would consist of. I was hoping only records with splits could have splits and use a bunch of complicated UNION's and apend delete queries and such to display my mess. Thanks for your response. Barry "Jeff Boyce" wrote: Barry While I admire your determination, I also wonder why you wish to recreate the wheel? If Quicken or QuickBooks can already handle "splits", why do you need to figure out a way to do that? Also, your opening phrase "a simple database" seems somewhat in conflict with the idea of incorporating a mechanism for "splits". (but if you are as determined as you seem to be, consider: One transaction will have one (or more) "splits". I'm defining a split as a portion of a check used for a different purpose, not a totally different check. This implies that you need a table to hold splits. That table might look something like: trelSplits SplitID TransactionID SplitAmt SplitDescription SplitCategoryID and you'd need to modify your Transaction table to include: tblTransaction TransactionID TransactionDate TransactionDescription You would not need to put the transaction total in, as it would (and should) be calculated as the sum of the splits. Note that this design basically requires EVERY transaction to have (at least) one split. Best of luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Barry A&P" wrote in message news I have a simple database for keeping my checkbook (or all finances) straightened out. and i am looking for suggestions on how to make my deposits splittable.. i have two simple tables T_Transactions TransactionID TransactionDate TransactionAmount TransactionDescription ExpenseCategoryID And T_ExpenseCategory CategoryID CategoryName IsTaxable when i enter a deposit the bank statement only shows one amount but it may actually be the sum of 6 checks all needing different categories assigned to them.. Does anybody have any neat ideas about how to setup a database that can do this. I know the basics pretty good but im not sure if i can figure out a good user interface for this.. or how to maintain good normalization if one transaction has no split details while others have many split details.. I remember years ago using quickbooks and there was a split button that sent you off somewhere. Any ideas would be greatly appreciated.. Thanks Barry . |
#4
|
|||
|
|||
Suggestions to Split Transaction details
Hi Barry,
Withdrawals (cash or checks) and Deposits (cash or checks) are both transactions. You need to think in terms of withdrawals and deposits rather than expenses and deposits. So rather than expense category you need a table of transaction category. The categories in this table would be types of expenses (debits) as well as sources of deposits (credits). Thus your tables should look like: TblTransaction TransactionID TransactionDate TransactionDescription TblTransactionDetail TransactionDetailID TransactionID DebitCredit (Use option Group where Debit = -1 and Credit = +1) TransactionCategoryID TransactionAmount TblTransactionCategory TransactionCategoryID CategoryName TransactionCategoryTypeID IsTaxable Include in TblTransactionCategory both your expense categories as well as your sources for deposits. If there are a large number of categories or even for just convenience, add a table for types of transaction categories: TblTransactionCategoryType TransactionCategoryTypeID TransactionCategoryType (Debit and Credit) TransactionCategoryMultiplier (-1 and +1) Then you don't need the DebitCredit field in TblTransactionDetail. I haven't included distinguishing between cash and checks in the above. If you need that detail you can expand upon the above. Also, if you have more than one bank account, you can expand upon the above. Steve "Barry A&P" wrote in message news I have a simple database for keeping my checkbook (or all finances) straightened out. and i am looking for suggestions on how to make my deposits splittable.. i have two simple tables T_Transactions TransactionID TransactionDate TransactionAmount TransactionDescription ExpenseCategoryID And T_ExpenseCategory CategoryID CategoryName IsTaxable when i enter a deposit the bank statement only shows one amount but it may actually be the sum of 6 checks all needing different categories assigned to them.. Does anybody have any neat ideas about how to setup a database that can do this. I know the basics pretty good but im not sure if i can figure out a good user interface for this.. or how to maintain good normalization if one transaction has no split details while others have many split details.. I remember years ago using quickbooks and there was a split button that sent you off somewhere. Any ideas would be greatly appreciated.. Thanks Barry |
Thread Tools | |
Display Modes | |
|
|