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?