Database design: Cash movement between accounts: Double linking tables

[Persist]

1Perhaps there are some web links that you can direct me to for assistance with this.


Using ms access 2007

Here are two ways I have tried to structure this database of cash movements between accounts.

Each transaction or deal can have two movements, e.g.
. move 1: from-account, minus $ 100.00
. move 2: to-account, plus $ 100.00

Some deals have three movements as consumption tax is levied, e.g.
Pay for a plumber
. move 1: Cheque-account, minus $ 220.00
. move 2: Maintenance-account, plus $ 200.00
. Move 3: Tax Paid-account, plus $ 20.00

Both of my attempts used an account table t-Account with
. Account code and
. Descriptor

***** My First attempt had one table t-Deal with
. nDeal
. Date
. Account1
. Income1
. Account2
. Income2
. Account3
. Income3
. Comment

This database is working – but I am using record-sets and SQL and a large temporary table t-Moves constructed from t-Deal
. Date
. Account
. Income
This enables me to calculate
. the sum of each account to get the current balance and
. the cumulative totals of each account

Each deal can easily be seen on one line of output. I want this feature.
This database works but is complex
I thought I could do it more simply – and got stuck – not so simple

**** My Second attempt: I tried a movement table and deal table

Movement table t-Move
. nMove
. nDeal – the deal of which this movement is a part – gives date
. Account
. Income

Deal table t-Deal
. nDeal
. Date
. nMove1
. nMove2
. nMove3
. Comment

Query q-View-Deal
. nDeal
. Date.
. nMove1
. Account1
Income1
. Ditto nMove2 and nMove3
. Comment

To calculate cumulative balances, I need each Move to know which deal it is part of so the date of the deal is known. That s why I have nDeal as part of t-Move

To present each deal on one line I need to know which moves are associated with each deal. This is why I put nMove1, 2 and 3 as part of t-Deal.

This double linking is giving me problems.

Problem with relationships table
How do I put these relationships into the relationships section
I think I want one-to-one relationship between
nMove1 in tDeal and nMove in tMove
The relationship section seems to have a mind of its own and puts 1 to infinity relationships sometimes.
I do not understand the categories of link that access offers.
I do not even see one-to-one as a possibility

Problem with Form Wizard generating a form based on q-View-Deal
Wizard produced a table I could see in design mode but could not see anything in View mode.
I would like a form that helped enter data by automatically entering the links between tables t-Deal and t-Move.

Is this second approach worth perusing? Can it be done?

[jzwp11]

Neither approach you show is valid based on normalization rules. If a deal/transaction has multiple accounts involved that is a one-to-many relationship. Further, an account can be involved in many deals/transactions, so another one-to-many relationship. If you have 2 one-to-many relationships between the same to entities (deals and accounts), you need a junction table.

tblTransaction
-pkTransID primary key autonumber
-dteTrans (transaction date)
other fields relative to the transaction

tblAccounts
-pkAcctID primary key, autonumber
-txtAcctName


tblTransactionDetails
-pkTransDetailID primary key, autonumber
-fkTransID foreign key to tblTransaction
-fkAcctID foreign key to tblAccounts
-currAmount

So if a transaction has 3 accounts involved, you would have 1 record in tblTransaction and 3 related RECORDS (not fields) in tblTransactionDetails

[Persist]

Thanks for your post

Your structure is flexible as it allows each Transaction to have many Transaction-Details
It is limiting as I was hoping to show each transaction on one line of a query, i.e.
Date, Account1, Money1, Account2, Money2, Account3, Money3, Comment

I want compact output.
Can your structure achieve this – in a query or form or report

My structure had a link in each transaction to each of its three transaction-details.
This made it easy to construct a query
– but it seems it broke database rules and Access could not cope with it.

[pbaldy]

FWIW, I wrote a rudimentary accounting app and used the type of structure jz suggested. That is the normalized design. You say you want that "compact" output, but how compact is it if there are more than a few accounts? You'd be scrolling off the end of the page. If you want it anyway, it could be achieved. One way would be modifying a function like this:

Return a concatenated list of sub-record values

[Persist]

Thanks. I am proceeding as suggested
I may try the concatented list of subrecord values

A query sorting by transaction shows e.g.

Transaction1, account5, income5, transaction1 description
Transaction1, account7, income7, transaction1 description
Tranaction2, account4, income4, Transaction2 description
Etc

This describes most transactions in two lines. I can live with this.

Of course, new concerns arise.

Most transactions involve only two accounts so I need to enter the same figure in twice, once positive and the second time negative.

In the second entry I can press “control” + “single quote” to copy the figure from the first entry into the second entry. Then I position the mouse to change the sign.

Is there and easy way to change the sign using keyboard shortcuts

I could enter the income as two factors
Factor1 = -1 Factor2 = 200

[Persist]

I found out how to do it.

I can enter the positive entry first
“Control” + “single quote” copies this first entry into my second entry
F2 puts the entry point to the end of the number just entered
After the number e.g. 50
I can type minus i.e. 50-
Which is interpreted as -50
Easy

[dcartford]

Try downloading this book from smashwords on accounting database design. There is a chapter that cover the design of cash module with query tutorial on generating cash flow reporting.

Good Luck!