Dynamic run on sum field in query

[Giloosh]

Hello i have been trying to figure out this problem for a very long
time now, and thought maybe its worth asking for some help.

I want to figure out if there is a way to create a field in a select
query that acts as a "COUNT" for the fields in the query.
No matter what order the rest of the fields will be it will always
remain in numerical order from 0+. Field one will be 1, field 2 will be
2, field 3 will be 3...etc.

The reason i need to know this is because i have a run on sum for a
balance field for a table called "tbltrans" where i have got it to
work, but only when the ID is in an assending order.

my sql statement:
-------------------------------------------------------------
SELECT t1.id, t1.charge, t1.pay, t1.date, [t1].[charge]-[t1].[pay] AS
due, Sum([t2].[charge]-[t2].[pay]) AS balance
FROM tbltrans AS t1, tbltrans AS t2
WHERE ((([t2].[id])<=[t1].[id]))
GROUP BY t1.id, t1.charge, t1.pay, t1.date, [t1].[charge]-[t1].[pay];
-------------------------------------------------------------

If i decide to change the order of any field than this changes the
order of the ID, and the balance since every field is linked to
eachothers order. I need the balance field which is a run on sum, to be
dynamic. This means it is not linked to the rest of the field. the
balance will change according to the order it is in.
Is there a way i can get a field that is constantly in numerical order
from 0+ and it will never change. It will not be linked to the rest of
the fields. Once i can accomplish that than in i can change my "WHERE"
SQL statement to be like:
(the new count field will be named [count] for this example.)
---------------------
WHERE ((([t2].[id])<=[t1].[count]))
---------------------

quick demo to explain what i mean:
ID ASSCENDING ORDER
id / charge / pay / balance
------------------------------
1 / 50 / 0 / 50
2/ 0 / 5 / 45
3/ 60 / 0 / 105
4/ 0 / 10 / 95

ID DESSCENDING ORDER
id / charge / pay / balance
------------------------------
4 / 0 / 10 / 95
3/ 60 / 0 / 105
2/ 0 / 5 / 45
1/ 50 / 0 / 50

Notice how the balance has followed the rest of the fields.
I want to get the balance to dynamically recalculate correctly.
If i succeed it should look like:

ID DESSCENDING ORDER *CORRECT WAY
id / charge / pay / balance
------------------------------
4 / 0 / 10 / -10
3/ 60 / 0 / 50
2/ 0 / 5 / 45
1/ 50 / 0 / 95

Also note that the laste field should always show the final sum of the
balance.
It is important i figure this out, so any help would be highly
appreciated!
Thanks,
Gil

[Giloosh]

i just found out a way to have a dynamic record counter in the query by
a previous post:
------------------------------------
Global gIncNum As Long

Function ZeroIncNum()
gIncNum = 0
End Function

Function IncNum(Dummy As Variant) As Long
gIncNum = gIncNum + 1
IncNum = gIncNum
End Function

'Then add two fields to your Query:
'Expr1: ZeroIncNum()
'RecNum: IncNum (ID)
------------------------------------------------------------
(ID means the tables ID that you want to count)
RecNum brings you the number of the record, but it doesnt work in a my
join query. It begins skipping numbers.
Any ideas?