> Hi, I think this is a very old question, but I never did it with
> Firebird
> before.
>
> I want to do a simpe current acount with in-out-balance columns. The
> movements
> would come from the invoices and payments tables ordered by date. I
> usually did
> it with triggers that insert the movements in a separate table and re-
> calculate
> all the balances (with a stored procedure) for each record every time
> something
> in inserted, modified or deleted. I wonder if there is a way to do it
> "on the
> fly".. I mean when the user want to see some current account, just do "
> select
> (some fields) from invoices union all select (some fields) from
> payments order
> by 1" (being "1" the pos of the date field). Firts of all, I don't know
> how fast
> would be this approach as the DB gets bigger, and second: I dont know
> how to
> resolve the "balance" column on a select, because it depends on the
> previous
> row. May I have some diferent opinions of experienced people? Thanks!!
> -s

when I do accounting systems, one of the first things I do is design around
a period (fiscal or calendar year). This makes for totaling much faster than
slowing and continually totalling over longer and longer periods.
Ever heard of "Closing the books". I'm not the only one to implement the
creation of past year's tables when the previous year is moved to a "closed"
tables which has a closing balance in it, and the current table has an
opening balance in it.
I also implement credit and debit columns. Don't put them in one column,
credit and debit can both be positive and negative and they mean something
different when you speak of journals.
Views can be used for the balance of which you speak and they can can be
very fast.
Alan