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 gkittelson 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
Sep 17, 2015
2
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

ravicoder
  • Locked
  • Question
Plotting position of text in an image in visual foxpro 1
Replies
9
Views
1K
tomk3
tomk3
bharons
  • Locked
  • Question
The conversion of the varchar value '1111224080294' overflowed an int column 1
Replies
12
Views
2K
Chris Miller
Chris Miller
Rajesh Karunakaran
  • Locked
  • Question
Select 1st record for each 'code' in table with multiple records for each 'code'
Replies
6
Views
386
Rajesh Karunakaran
Rajesh Karunakaran
SitesMasstec
  • Locked
  • Question
How to order a column of data in a Grid
Replies
3
Views
552
EinTerraner
EinTerraner
Dronner
  • Locked
  • Question
How do I get the column number of a cell in a foxpro grid Visually?
Replies
5
Views
388
Dronner
Dronner

Part and Inventory Search

Sponsor

Back
Top