How can I write this summing query?

Question

I didn't design this table, and I would redesign it if I could, but that's not an option for me.

I have this table:

Transactions
    Index  --PK, auto increment
    Tenant --this is a fk to another table
    AmountCharged
    AmountPaid
    Balance
    Other Data

The software that is used calculates the balance each time from the previous balance like this:

previousBalance - (AmountPaid - AmountCharged)

Balance is how much the tenant really owes.

However, the program uses Access and concurrent users, and messes up. Big time.
For example: I have a tenant that looks like this:

Amount Charged | Amount Paid | Balance
      350            0            350
      440            0            790
       0            350          -350      !
       0            440          -790

I want to go though and reset all the balances to what they should be, so I'd have some sort of running total. I don't know if Access can use variables like SP's or not.

I don't even know how to start on this, I'd assume it'd be a query with a subquery to sum all the charges/payments before it's index, but I don't know how to write it.

How can I do this?

---

Edit:

I am using Access 97

Answer 1

If all the records have a sequnecing number (with no gaps in between) you can try the following: create a query where you link the table to itself. In the join, you spicify that you want to link the tables with Id = Id - 1. That way, you link each record to its previous record.

If ou do not have a column that can be used for this, try adding an autonumber column.

Other option is to write some simple lines in VBA to loop over the records and update the values. If it is a one-off operation, I think that will be the easiest if you are not very experienced with sql.

Answer 2

Assuming Index is incremental, and higher values --> later transaction dates, you can use a self-join with a >= condition in the join clause, something like this:

select 
  a.[Index], 
  max(a.[Tenant]) as [Tenant], 
  max(a.[AmountCharged]) as [AmountCharged],
  max(a.[AmountPaid]) as [AmountPaid],
  sum(
    iif(isnull(b.[AmountCharged]),0,b.[AmountCharged])+
    iif(isnull(b.[AmountPaid]),0,b.[AmountPaid])
    ) as [Balance]
from 
  [Transactions] as a
left outer join 
  [Transactions] as b on
    a.[Tenant] = b.[Tenant] and
    a.[Index] >= b.[Index]
group by 
  a.[Index];

Access SQL is fiddly; there may be some syntax errors above, but that's the general idea. To create this query in the query designer, add the Transactions table twice, join them on Tenant and Index, and then edit the join (if possible).

You could do the same with a subquery, something like:

select 
  [Index], 
  [Tenant], 
  [AmountCharged], 
  [AmountPaid], 
    (
      select 
        sum(
          iif(isnull(b.[AmountCharged]),0,b.[AmountCharged])+             
          iif(isnull(b.[AmountPaid]),0,b.[AmountPaid])
          )
      from 
        [Transactions] as b
      where 
        [Transactions].[Tenant] = b.[Tenant] and 
        [Transactions].[Index] >= b.[Index]
    ) as [Balance]
from 
  [Transactions];

Once you have calculated the proper balances, use an update query to update the table, by joining the Transactions table to the select query defined above on Index. You could probably combine it into one update query, but that would make it more difficult to test.