Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum Column At Each Value Change of another field in foxpro

Status
Not open for further replies.
Katarina2016

Katarina2016

Programmer
Joined
Sep 17, 2015
Messages
2
Location
RS
Hello, I am working on FoxPro 2.6 for DOS system. How can I get to add column (summ)? How can I get it to start at each item change?
E.g.
ID...quantity...summ
=============================
650.....50........50
650....100.......150
650.....80.......230
650....500.......730
115.....70........70
115.....20........90
115....150.......240
115.....60.......300
115....430.......730

Code:
close all
delete file("x.dbf")
select ID,quantity,round(00000.0,2) as summ from data;
into table x

total = 0
scan
total = total + quantity
replace summ with total
endscan

Thanx in advance,
Cathy
 
Sort by date Sort by votes
You'd not do such calculations in a table, but in a report grouoped by ID, summin quantity and outputting the running sum in each detail and the total in each group footer.

Bye, Olaf.
 
Upvote 0 Downvote
Hi,
Please have a look at the code below - now it works

Code: 
LOCAL ARRAY laCalc[1,3]
LOCAL iIntSum

CREATE CURSOR Total_sales (cName C(15), iSales I)

INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)

SELECT *, iSales AS ST FROM Total_Sales ORDER BY cName INTO ARRAY laCalc

iIntSum = laCalc[2]

For i = 4 to ALEN(laCalc) STEP 3
	If laCalc[i - 3 ] = laCalc[i] 
		laCalc[i - 1] = iIntsum
		laCalc[i + 2] = iIntsum + laCalc[i + 1]
		iIntSum = laCalc[i + 2]
		
	ELSE
	
	iIntSum = laCalc[i + 1]

	EndIF
ENDFOR

CREATE CURSOR Total_Sales2 (cName C(15), iSales I, iTotalSales I)
APPEND FROM ARRAY laCalc
LOCATE
BROWSE


CLOSE ALL

hth

MK
 
Upvote 0 Downvote
Katarina2016,

You can save a temporary value and watch for it to change. Something like this:
Code: 
close all
delete file("x.dbf")
select ID,quantity,round(00000.0,2) as summ from data;
into table x

total = 0
[COLOR=blue]GO TOP
STORE id TO tmpID[/color]
scan
total = total + quantity
replace summ with total
[COLOR=blue]IF ID # tmpID
   total = 0
ENDIF[/color]
endscan

-Dave Summers-
[cheers]
Even more Fox stuff at:
 
Upvote 0 Downvote
Hi Cathy,
Either use arrays (see my previous post) or something similar to the code below

Code: 
LOCAL iIntSum, lcName

CREATE CURSOR Total_sales (cName C(15), iSales I)

INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 45)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stella" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 24)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Johnny" , 30)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 450)
INSERT INTO Total_Sales (cName, iSales) VALUES ("John" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 15)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jenny" , 10)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Georges" , 52)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Jerome" , 1112)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Xilla" , 1612)


SELECT *, iSales AS iTSales FROM Total_Sales ORDER BY cName INTO CURSOR SalesWithSum READWRITE 

SELECT SalesWithSum

iIntSum = 0
lcName = cName
SCAN
	IF cName = lcName
	
		iIntSum = iIntSum + iSales
		replace iTSales WITH iIntSum
	ELSE
		
		lcName = cName
		iIntSum = iSales
	ENDIF 
ENDSCAN 

LOCATE
BROWSE

CLOSE ALL

hth
MK

p.s. I'm sorry Dave but your code gives wrong results
 
Upvote 0 Downvote
Thank you very much for code, mjcmkrsr!

This is a good idea, I don't know how it didn't cross my mind. Here...
============================
lcName = cName
.....
IF cName = lcName
ELSE lcName = cName
.....
============================


p.s.Too, thanxs for another.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Question Question
How to interactively change control source of a grid at runtime?
Replies
14
Views
519
Chriss Miller
C
SitesMasstec
  • Locked
  • Question Question
Sum of values in a Report Detail band
Replies
11
Views
1K
igorsandler
igorsandler
C
  • Locked
  • Question Question
foxpro print
Replies
4
Views
664
xWongso
xWongso
ravicoder
  • Locked
  • Question Question
animated gif in visual foxpro form
Replies
13
Views
1K
Chriss Miller
C
ajenat
  • Locked
  • Question Question
Include non-foxpro EXE within Foxpro EXE
Replies
5
Views
939
Chriss Miller
C

Part and Inventory Search

Sponsor

Back
Top