Should I separate this data?

[GrantSymon]

Hi,

I'm using FMPro 7 to build a simple accounting system for my business (I'm a photographer). I have 5 bank accounts in 3 currencies to cater for and my accountant has asked me to make it effective for 'double entry' accounting.

I have a table for debit and another for credits and yet another for 'position at bank' (the 'starting point' for bank balance calcs).

I will need to produce reports for my accountant, with a running balance of my bank accounts and it would seem best to me that they be date sorted with debits and credits intermingled as and when they happen and with the corresponding bank balances calculated and displayed. I envisage all the currencies being mixed in a single report (but obviously should it be useful, they could be separated using Finds).

My question is ;

___________________________________________________________________

Is it better to have a separate table for each bank account, which would include both credit and debit records for that account?

or

Should I have everything in a single table, all bank accounts and all debits and credits?

or

Should I use just 2 tables : i.e. all the credits, from all 5 bank accounts, in one table and all the debits, from all 5 bank accounts, in another?

____________________________________________________________________

Any help will be much much appreciated ... I seem to be spending a huge amount of time going round in circles, unable to decide how I *should* be doing this and consequently wasting time doing work I'm not going to use

Many thanks,

Grant

[pcourterelle]

First, it seems like you're recreating the wheel here. If you do any online banking you can access, print and/or email exactly this type of information in the format you describe with the online tools the banks supply. It seems that the simplest solution is to forward information direct from your bank to your accountant rather than create a db to track info that the bank is already providing you. Some banks even allow you to download the info for use in spreadsheets.

Be that as it may....

you asked:

Is it better to have a separate table for each bank account, which would include both credit and debit records for that account?

No. This simply complicates matters and makes entering your data a pain. Unnecessary duplication.

you asked:

or

Should I have everything in a single table, all bank accounts and all debits and credits?

Yes, this is how I think it should be done and how I would approach it. You create a unique identifier for each bank account, as well as fields for debits, credits, summary calculations to calculate your openning and closing balances. This will allow you to create searches, tables and reports to satisfy you accountant's need for "double entry" accounting.

you asked:

Should I use just 2 tables : i.e. all the credits, from all 5 bank accounts, in one table and all the debits, from all 5 bank accounts, in another?

Certainly workable but not as streamlined.

Question: why is your accountant having you do all the "double entry" accounting stuff? Isn't this what your paying the accountant to do? Just curious.

Cheers

pc

[GrantSymon]

Hi pc,

thanks for the reply!

I've been agonizing over this for about 3 weeks. I started off on the 3rd system, which seemed like a good way ... just because I have this idea that it's good to keep data separated ... but when I started to try and build the reports layout, I realised that I had no idea how to combine the data from 2 tables into a single report.

I shall now get my teeth into my project using the 2nd option.

(OFF-topic :

To answer your question about re-inventing the wheel ... on this side of the pond, things are not quite so easy. My bank, one of the major clearing banks in the UK, does not provide online banking for foreign currency accounts, so my USD and Euro accounts can't be accessed on the net. Also, they do not provide any feedback that I would need for book-keeping, since this is knowledge only I have. This also touches on your last question ... why double-entry?

Effectively, I'm doing the book-keeping myself. I used to pay someone to do this, who would then pass on beautifully hand-written books to my accountant, in which they had carefully calculated the double-entry data, against my bank statements, from the large plastic bag of petty-cash receipts and invoices which I'd given them. Nowadays, it seems acceptable to deliver a computer generated report instead, so I've cut-out the bookeeper, and now I do it myself (or rather my assistant does!). It's pretty straightforward really and it would be an uneccessary expense to pay my accountant to do it and ... I get to have *fun* writing another database!)