Crystal Reports my balance at a certain date

Message

     hi, 

I have a table (transactions) recording the activities done on the cash accounts like

account1   credit 2000 USD   at 02/04/2009
account1   debit 100  USD     at 05/06/2009
account1   credit 3000 USD   at 10/10/2009

and I have the information that my balance is 4900 USD recorded at other table (accounts)

the question that I need the balance at a certain date, for example I need the balance at 05/06/2009

and to do this I need to do one of tow solutions:

solution #1:

sumrize all the activities from the first record in the table for that account till I reach the reqired date
but this solution will cause  a performance issues and after a while some of the record will be removed for space issues.

solution # 2:

start from the balance recorded at the table (accounts) and go to the last record at the table (transactions) and subtract the amounts recored till I reach the rquired date

I need your help to tell me how can I do the second solution if possible.

thanks in advance 

regards,
Thamer

Edited by seagull - 21 Oct 2009 at 12:37am