如何编写此求和查询？

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:

但是,该程序使用Access和并发用户,并且搞砸了。重要时刻。例如:我有一个看起来像这样的租户:

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.

我想通过将所有余额重置为它们应该是什么,所以我有一些运行总计。我不知道Access是否可以使用SP之类的变量。

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

我正在使用Access 97

2 个解决方案

#1

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:

假设索引是增量的,更高的值 - >更晚的事务日期,您可以在join子句中使用带有> =条件的自联接,如下所示:

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).

Access SQL很繁琐;上面可能有一些语法错误,但这是一般的想法。要在查询设计器中创建此查询,请将“事务”表添加两次,将它们连接到“租户”和“索引”,然后编辑连接(如果可能)。

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.

计算出适当的余额后,使用更新查询来更新表,方法是将Transactions表连接到上面在Index上定义的select查询。您可以将它组合成一个更新查询,但这会使测试更加困难。

#2

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.

如果所有记录都有一个序列号(两者之间没有间隙),您可以尝试以下操作:创建一个查询,将表链接到自身。在连接中,您想要使用Id = Id - 1链接表格。这样,您将每条记录链接到其先前的记录。

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.

其他选项是在VBA中编写一些简单的行来循环记录并更新值。如果是一次性操作,我认为如果你对sql不是很有经验,这将是最简单的。

#1