"Running Balance" query

Hi,

I'm still in the data modelling stage, and have read over some general ledger database design principles, but wondering if someone can help with a query I'm looking to generate.

Currently, all transactions would be placed in the Transactions table, which is the basic equivalent to a general ledger. I'm not maintaining a running balance column, since it can be calculated. But of course, one of the main screens/reports will be an account history with the running balance to date beside each transaction.

Is there a way, while the SQL statement is grabbing the transaction table (sorted by Date) to keep a running tally of the Amount column? Otherwise, I'd have to grab the table without the running balance, and do another query for each transaction - summing the Amounts before the current transaction date. Or just do it in code with the returned table.

Any suggestions?

Thanks,
Brian

I'd probably try to do it in the application instead of in the database, but if you've got to do it in the database, you could use a cursor to loop through each record and calculate the running balance. I'd stay away from the cursor for performance reasons, but I think it would still be better than executing a separate SQL query for each transaction.

Lucas Alexander
http://www.alexanderdevelopment.net

Sounds good. Was hoping there was a way to do it, since it's needed in every account program (that I know of). There's probably a way to redesign the database around it, but there's probably no point for this one issue.

Thanks again,
Brian