I have two tables at the heart of an accounting system I built in Access. [Transactions] is a list of transactions used (via an export to excel and a pivot table) to create cash-flow reports and forecasts, [Statements] is a list of actual transactions downloaded from my online banking. They are in a 1-to-1 relationship.
I do it this way because it makes the task of importing data from my online banking easier. [Statements] is in a format I can simply copy and paste into from my online banking, also it makes it easier to have transactions which are entered as forecasts initially but automatically show the actual value and date instead once I have linked that transaction to the corresponding statement. I use a set of queries to pick up those transactions which have been linked with statements and show the actual values in my reports.
But there are problems with the one-to-one relationship and I want to change it. Sometimes a single payment can be for more than one item, and in those cases I would want to have a one statement - many transactions relationship.
However the problem with this one-to-many relationship is that the field which sets the relationship would have to be in the [Transactions] table, and when adding new statements to my database and then linking them up with transactions, it is easier for me to be working in the [Statements] table because all statements must be linked to a transaction, but not all transactions must be linked to statements (lots of transactions are in the future).
In [Statements] I can see which statements haven't been linked yet, and then select the appropriate transaction from a drop down list. If I did it the other way round, then there are no fail-safes to prevent me forgetting to link all the statements.
What do people suggest? I've been using the system for a while now, I know I should have foreseen this and I know we shouldn't be messing around with working databases.
I do it this way because it makes the task of importing data from my online banking easier. [Statements] is in a format I can simply copy and paste into from my online banking, also it makes it easier to have transactions which are entered as forecasts initially but automatically show the actual value and date instead once I have linked that transaction to the corresponding statement. I use a set of queries to pick up those transactions which have been linked with statements and show the actual values in my reports.
But there are problems with the one-to-one relationship and I want to change it. Sometimes a single payment can be for more than one item, and in those cases I would want to have a one statement - many transactions relationship.
However the problem with this one-to-many relationship is that the field which sets the relationship would have to be in the [Transactions] table, and when adding new statements to my database and then linking them up with transactions, it is easier for me to be working in the [Statements] table because all statements must be linked to a transaction, but not all transactions must be linked to statements (lots of transactions are in the future).
In [Statements] I can see which statements haven't been linked yet, and then select the appropriate transaction from a drop down list. If I did it the other way round, then there are no fail-safes to prevent me forgetting to link all the statements.
What do people suggest? I've been using the system for a while now, I know I should have foreseen this and I know we shouldn't be messing around with working databases.