Two possible approaches to calculating the running balance per record
that you could look at are:
a) use a 'selectable stored procedure' (& calculate the running balance
balance per record therein),
b) use the caller (Delphi, VB, Crystal Reports etc) to calculate the running
balance component of the query.
HTH,
John

> -------Original Message-------
> From: Alan McDonald <alan@...>
> Subject: RE: [firebird-support] advice on mantaining a current account
> Sent: 10 Oct '08 06:54
>
> > 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
>