I have a standard Transactions table with debits and credit. I need a report that shows a running balance after each transaction. In Excel, this would be something like the following (B: Debits, C: Credits, D: Balance) D5=D4-B5+C5. I've seen how to do sums in reports, but not sure how to incorporate adding and subtracting at the row level. Thanks.
-
-
Not so easy, this one, as in Excel it is very natural to refer to the previous balance (D4 in your example) - but in SQL there is no concept of row position, so extracting the previous balance to form a running sum of balances is tricky.
It is a simple matter to sum a whole group, but providing a running SUM showing the changing balance for each transaction involves joining a table back to itself on the last transaction somehow, and is not one for which an easy solution comes to mind. It can be done, but there has to be some means to join the tables by 'staggering' the rows using transaction dates or identifiers to imply the order so that the current joins the previous, the previous the one before, and so on.
Do you really need this in Access? If you do, and you can't live without it, we will need the metadata (the field names, types and keys involved) for the table concerned to play with some SQL and see what happens.
-Stewart -
I didn't really think about it, but I can just export the data to Excel. It isn't for something very formal, so I don't need to make a fancy report.Comment
-
Actually it should be fairly simple.
Queries don't support this facility, but TextBox controls in Access Reports have the Running Sum attribute. This typically works within a group, but if it's required to work across multiple groups then a little imagination is all that's required to make it work ;)Comment
Comment