hi! i'd like to sum amounts from a

[ahmed1973]

hi!
i'd like to sum amounts from a table (field) for each 6 records and put the sum result in a textbox.

 

[Olaf Doschke]

One textbox can display one sum, so you mean a textbox for each 6th row? You'd still need a full column and a field you only fill in for each 6th record.

I would suggest this:

select *, cast(0 as B) as RunningTotal  from table into cursor curRunningTotal readwrite
lnRunningTotal = 0
scan
  lnRunningTotal = lnRunningTotal + field
  Replace RunningTotal with m.lnRunningTotal in curRunningTotal
endcan

Now display that in a grid and set dynamicforecolor = iif(recno()%6=0,0,rgb(255,255,255))

Now what does this do?

That does put a running total into an additional field called RunningTotal into a cursor and while that running sum is computed for each row, the dynamic fore color of that grid column will only be black each 6th row.

It does not make sense to use a table or a grid like an excel sheet. In fact I'd see a more ideal solution to only add this sum each 6th row in an excel export, not inside your application. Or do that in a report, but not in a form. On a form you don't need such a running total. I see an aspect of manual control, manual verification of the calculation by smaller subtotals in each 6th row. But you do sum in a program, and summing doesn't fail in comparison with manual summing, so such layouts you may know from your business don't make much sense in a program.

But that last criticism is just my opinion. You know best why you need this.

I may have gotten your request wrong overall, then please be more verbose on what you really want.

Bye, Olaf.

 

[ahmed1973]

i'd like to do a code like this
SELECT 2
use Table
GO top
i1=0
vmont=0
DO WHILE !EOF()
vmont=vmont+table.field &&(amount)
i1=i1+1
skip
....
....
here I want to make a condition if the recordcount=6 I display the vmont in a textbox1
and it will repeated until end of file

 

[Olaf Doschke]

Then you already know all you need, don't you? What is missing, what do you need help with, now?

you set a textbox value by setting it's Value property
Recno() is giving you the record number and %6 is modulus 6, which is 0 each 6th record, eg for records number 6,12,18,etc.

What else do you need?

Bye, Olaf.

 

[ahmed1973]

what s the program that knows records number 6,12,18,etc

 

[Olaf Doschke]

I said it twice already...

if recno()%6 = 0
   * do what you want, if you're at records 6,12,18,..
endif

Bye, Olaf.

 

[Olaf Doschke]

And if you don't know the math of modulus and don't know about recno(), then why don't you use another counter variable and do

counter6=0 && init to 0
DO WHILE !EOF()
...
counter6 = counter6+1
if counter6=6
   counter6=0 && reset to 0
   * do what you want, if you're at records 6,12,18,..
endif
ENDDO

You have to understand the code you maintain, in the end. It doesn't matter, if there are more elegant and shorter ways. But if you want to use something new, use SCAN..ENDSCAN instead of DO WHILE !EOF()..SKIP..ENDDO.

Bye, Olaf.

 

[Olaf Doschke]

And a final comment (sory, if I'm getting on your nerves): If you want to do this to show progress of the summation to an impatient user, first try, if that's needed at all, summing is fast. You would need in the range of millions of records, to make this need much more than a second, but you will force it to take longer, if you update a textbox while computing the total.

SET TALK OFF
CALCULATE SUM(field) TO thisform.textbox1.value

or

Select Sum(field) From Table Into Array laSum
thisform.textbox1.value=laSum

Both should just take split seconds.

Bye, Olaf.

 

[Dan Freeman]

Since CALCULATE accepts a scope and it isn't an SQL command, you could even include the "every 6th record" criteria:

CALCULATE SUM(field) TO thisform.textbox1.value FOR MOD(RECNO(),6)=0

 

[Olaf Doschke]

Dan,

that's cool, but I think Ahmed needs the sum of all records so far in each 6ths row and the way you do it the sum would only sum each 6ths value. So in the end Ahmed needs just a normal total.

Or I am still not really getting and understand what he wants.

Bye, Olaf.