Suggested table structure for an accounting system (1 Viewer)

[wmphoto]

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.

 

[John Big Booty]

You might get some inspiration from one of the data models here.

However you might find it far easier to purchase an off the shelf accounting system like QuickBooks for example.

 

[Opcode]

I was going to ask a similar question for my project. Should I create a new thread for my question?

 

[wmphoto]

to Opcode, I would create a new thread unless your question is exactly the same as mine, if there are subtle differences between what you ask and what I've asked then it becomes difficult to see what's what.

 

[wmphoto]

You might get some inspiration from one of the data models here.

However you might find it far easier to purchase an off the shelf accounting system like QuickBooks for example.

Thanks, I did have a look at the data models but none of them addressed my exact requirement. I know that I will probably have to either do one or the other of the two things I mentioned in my original post, but wasn't sure which would be best. I can't think of a third way anyway. I think wanting to have a many-to-one relationship that can be managed from the 'one' side is the database equivalent of having your cake and eating it

I do already use QuickBooks for my personal accounts, and for the initial download of bank statements from my bank, and then export from that to Access because QuickBooks doesn't do everything I want it to do. I like using Access because there is no limit (within reason) to what it can do.

 

[dcartford]

Correct me if I'm wrong, from your description, it seems you are trying to generate a cash flow report from these two tables. If that's the only report you are concerned with, try doing this way, create one table for storing the bank's particulars, e.g. banker name, address, contact, account number, a one GL table with fields created for receipt and payments. Set a PK field in your bank table and make reference to a FK field in your GL table. You can use the cheque number or a unique identifier as the key referential link between these two tables. With these, you can start scripting for a bank reconciliation and a cash flow statement on the fly. I can't elaborate further in this forum, but if you are still having doubts on how to do this, try downloading the accounting database design ebook from accountingdes.com