you need to create a calculated ID based on concatenating the columns that matter - in your example date and id e.g.
tmpID: clng(clng(date) & id)
then you can calculate your running sum based on that.
so your first query would be something like
Code:
select *, clng(clng(date) & id) as tmpID FROM myTable
- note the &, you don't add
and then a second one to calculate the values. Something like
Code:
Select qry1.*, sum(qry2.amount) as balance
FROM qry1 AS Qry2 INNER JOIN qry1 On qry2.tmpID<=qry1.tmpID
Note this sql uses an alias to call the same qry1 twice and by using the <= in the join, can only be written in sql, the query builder won't handle to join. But a cheat is to use the query builder, join on tmpID, then go to the sql window and change the = to <=, but make sure you have it the right way round
alternative is to use a cartesian query which can be done in the query builder
Code:
Select qry1.*, sum(qry2.amount) as balance
FROM qry1 AS Qry2, qry1
WHERE qry2.tmpID<=qry1.tmpID
or you can use sub queries or domain queries but they will be slower still.