A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Suggestions to Split Transaction details



 
 
Thread Tools Display Modes
  #1  
Old April 2nd, 2010, 10:32 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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  
Old April 2nd, 2010, 10:42 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old April 3rd, 2010, 12:16 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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  
Old April 3rd, 2010, 12:34 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.