SQL & Balance Field ???

[BlackRose]

hi...
I have a field with number
I need to make beside it another one
with formule to get the balance
any one have a good function

exp.

field1 field2
1 1
2 3
4 7

thanks 4 any advice .

[edwardo]

What do you mean by : "Balance Field"?
A counter of records you mean?

[BlackRose]

hi again...
Assume I have Table (T1) with one Field (F1)
F1 have a Amounts
______
F1
100
200
150
...
how can I create another field F2
by SQL Statment give me this result
F1 -F2
100-100
200-300
150-450

...
hope this clear for you

[edwardo]

Hi...

You explain yourself by giving only examples, but i thing I understud you.

You want the second field to have the SUM of all the numbers from the first field - from the current record and backward... right?

[edwardo]

Hi…

I assume you already know how to make and open a recorset…

* Opening a connection to the database

* Opening a recordset : Lets say with a SQL statement – “SELECT F1 FROM T1”

The recordset name, lets say is: rstF1
-------------------------------------------------------
Dim F2() as long
Dim x as long
Dim I as long

X = rstF1.recordcount ‘The number of records there are.
ReDim F2(x-1) ‘I discremented one coz the array count starts from 0.

F2(0) = rstF2(0) ‘ The value of the first record will be the same in F2 and F1.

For I = 1 to (x-1)
rstF1.movenext ‘Moving to the next record
F2(I) = F2(I-1) + rstF1(0)
Next

‘ Now in the dynamic array called F2 you got your F2 wanted field, If you want you can upload all the contents of the array to the DataBase with a loop, But I don’t think you’ll need it coz this numbers are a calculation from the F1 field and putting another field called F2 on the DB is a waste of space.

About the LOOP: I hope it’s ok, coz I didn’t tested it, if there are bugs then sorry…

Hope it will help.

Edy