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 Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Total Cumulé

Status
Not open for further replies.
jamal1971

jamal1971

IS-IT--Management
Apr 13, 2011
1
MA
Bonsoir à toutes et à tous, sincèrement j ai trouvé ce site très très intéressant.
Toute fois j ai un probléme j ai une vue :
Etat_de_compte_client(client,date,typepiece,reference piece, debit , credit )

j aimais bien ajouter une colone solde, dont la valeur est un total cumulé pour chaque client


exple si j ai une vue :
client date typepiece reference debit credit
A 01/01/2011 Achat 2415455 5.000 -
A 01/01/2011 reglement 2415MP - 2.500
A 01/01/2011 reglement 2415MP - 2.500

B 01/01/2011 Achat 2415455 15.000 -
B 01/01/2011 reglement 2415MP - 8.000
B 01/01/2011 reglement 2415MP - 3.500

je cherche à avoir une vue qui me donne les valeurs suivantes,
avec la colonne solde calculé de la forme suivante
client date typepiece reference debit credit SOLDE
A 01/01/2011 Achat 2415455 5.000 - 5.000
A 01/01/2011 reglement 2415MP - 2.500 2.500
A 01/01/2011 reglement 2415MP - 2.500 0

B 01/01/2011 Achat 2415455 15.000 - 15.000
B 01/01/2011 reglement 2415MP - 8.000 7.000
B 01/01/2011 reglement 2415MP - 3.500 3.500



MERCI INFINIMENT POUR VOTRE AIDE
 
Sort by date Sort by votes
Hi Jamal,
Have a look at the code below. Maybe it's what you'relooking for.


*****
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
Read Events
Close all
Clear All
RETURN


**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid with calculated columns"
MinHeight = This.Height
MinWidth = This.Width

ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 10, ;
Top = 30, ;
Width = ThisForm.Width - 20, ;
Height = ThisForm.Height - 40, ;
RecordSource = "curTemp"

ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 10, Top = 5, Height = 20

PROCEDURE cmdDoit.Click()
Local Array laCalc[1,1]

Select f1, f2, f3, 0 as f4 from curTemp into Array laCalc

For i = 1 to ALEN(laCalc, 1)
laCalc[i, ALEN(laCalc, 2)] = laCalc[i, ALEN(laCalc, 2) - 2] - laCalc[i, ALEN(laCalc, 2) - 1]
EndFor

For i = 2 to ALEN(laCalc, 1)
If laCalc[i , 1] = laCalc[i - 1 , 1]
laCalc[i, ALEN(laCalc, 2)] = laCalc[i, ALEN(laCalc, 2)] + laCalc[i - 1, ALEN(laCalc, 2)]
EndIF
EndFor

CREATE CURSOR curTemp2 (f1 I, f2 I, f3 I, f4 I)
APPEND FROM ARRAY laCalc
LOCATE

With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "curTemp2"
.SetAll("DynamicBackColor", "IIF(MOD(F1, 2)!= 0, RGB(255,255,255), RGB(0,255,0))", "Column")
EndWith

ThisForm.Refresh()
ENDPROC

PROCEDURE Resize
This.Grid1.Width = ThisForm.Width - 20
This.Grid1.Height = ThisForm.Height - 40
ENDPROC

PROCEDURE Destroy
CLOSE ALL
Clear Events
ENDPROC

PROCEDURE grid1.Init
WITH This.Column1
.ControlSource = "F1"
.Header1.Caption = "F1"
ENDWITH

WITH This.Column2
.ControlSource = "F2"
.Header1.Caption = "F2"
ENDWITH

WITH This.Column3
.ControlSource = "F3"
.Header1.Caption = "F3"
ENDWITH

*!* WITH This.Column4
*!* .ControlSource = "f1*f2"
*!* .Header1.Caption = "Product"
*!* ENDWITH


ENDPROC

PROCEDURE Load
LOCAL i
CREATE CURSOR curTemp (f1 I, f2 I, f3 I)
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 1, i*2, i)
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 2, i*3, i*2)
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 3, i*4, i*2)
ENDFOR
LOCATE
ENDPROC

ENDDEFINE
*********************************************


hth

Mark
 
Upvote 0 Downvote
or this (small adjustment)


*****
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
Read Events
Close all
Clear All
RETURN


**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid with calculated columns"
MinHeight = This.Height
MinWidth = This.Width

ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 10, ;
Top = 30, ;
Width = ThisForm.Width - 20, ;
Height = ThisForm.Height - 40, ;
RecordSource = "curTemp"

ADD OBJECT cmdUnDoit AS CommandButton WITH ;
Left = 120, Top = 5, Height = 20, Caption = "UnDo It"

PROCEDURE cmdUnDoit.Click()
With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "curTemp"
.SetAll("DynamicBackColor", "IIF(MOD(F1, 2)!= 0, RGB(255,255,255), RGB(0,255,0))", "Column")
EndWith

ThisForm.Refresh()
ENDPROC

ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 10, Top = 5, Height = 20, Caption = "Do It"

PROCEDURE cmdDoit.Click()
Local Array laCalc[1,1]

Select f1, f2, f3, f2 - f3 as f4 from curTemp into Array laCalc

For i = 2 to ALEN(laCalc, 1)
If laCalc[i , 1] = laCalc[i - 1 , 1]
laCalc[i, ALEN(laCalc, 2)] = laCalc[i, ALEN(laCalc, 2)] + laCalc[i - 1, ALEN(laCalc, 2)]
EndIF
EndFor

CREATE CURSOR curTemp2 (f1 I, f2 I, f3 I, f4 I)
APPEND FROM ARRAY laCalc
LOCATE

With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "curTemp2"
.SetAll("DynamicBackColor", "IIF(MOD(F1, 2)!= 0, RGB(255,255,255), RGB(0,255,0))", "Column")
EndWith

ThisForm.Refresh()
ENDPROC

PROCEDURE Resize
This.Grid1.Width = ThisForm.Width - 20
This.Grid1.Height = ThisForm.Height - 40
ENDPROC

PROCEDURE Destroy
CLOSE ALL
Clear Events
ENDPROC

PROCEDURE grid1.Init
WITH This.Column1
.ControlSource = "F1"
.Header1.Caption = "F1"
ENDWITH

WITH This.Column2
.ControlSource = "F2"
.Header1.Caption = "F2"
ENDWITH

WITH This.Column3
.ControlSource = "F3"
.Header1.Caption = "F3"
ENDWITH

*!* WITH This.Column4
*!* .ControlSource = "f1*f2"
*!* .Header1.Caption = "Product"
*!* ENDWITH


ENDPROC

PROCEDURE Load
LOCAL i
CREATE CURSOR curTemp (f1 I, f2 I, f3 I)
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 1, i*2, i)
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 2, i*3, i*2)
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 3, i*4, i*2)
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 4, Int(rand()*100), int(rand() * 50))
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 5, Int(rand()*89), int(rand() * 42))
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 6, Int(rand()*92), int(rand() * 45))
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 7, Int(rand()*1000), int(rand() * 500))
ENDFOR
FOR i = 1 TO 5
INSERT INTO curTemp VALUES ( 8, Int(rand()*10000), int(rand() * 5000))
ENDFOR
LOCATE
ENDPROC

ENDDEFINE
*********************************************

hth

Mark
 
Upvote 0 Downvote
MikeLewis,

Just a a note on your suggestion, the Autofox.org (or com) is repository of solutions rather than a ask-answer type of site, perhaps a better place would be the newsgroup of tha association which it here:
news://news.atoutfox.org/atoutfox.public.association


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)
 
Upvote 0 Downvote
Hi Jockey,

- I checked the link - but the running total example does not work (at least not with VFP6 or VFP9 SQL).



*** Test code ***

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

INSERT INTO Total_Sales (cName, iSales) VALUES ("Greg" , 50)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Sophie" , 20)
INSERT INTO Total_Sales (cName, iSales) VALUES ("Stalla" , 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)


SELECT a1.cName, a1.iSales, SUM(a2.iSales) Running_Total ;
FROM Total_Sales a1, Total_Sales a2 ;
WHERE a1.iSales <= a2.iSales or (a1.iSales=a2.iSales and a1.cName = a2.cName) ;
GROUP BY a1.cName, a1.iSales ;
ORDER BY a1.iSales DESC, a1.cName DESC;

CLOSE ALL

***End***

- It works with their data, but they seem to be chosen on purpose!

... and I did not yet find a SQL way to answer Jamal's question.

Mark
 
Upvote 0 Downvote
Mark,

confirmed the code is no good. (Should have checked it before posting a link here) Meanwhile after re-reading the original question I think Jamal is not looking for a running number but for a running balance.

Jockey(2)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

madalini
  • Locked
  • Question
Add sum/total on header 3
Replies
14
Views
527
madalini
madalini
icecr3amc4k3
  • Locked
  • Question
calculate the total number in the microsoft foxpro listbox
Replies
2
Views
123
Chris Miller
Chris Miller
Mandy_crw
  • Locked
  • Question
How to compute for total price in a grid? 3
Replies
12
Views
92
Chris Miller
Chris Miller
Bondjames
  • Locked
  • Question
TO MAKE PRG WITH TOTALS AND CASE 3
Replies
9
Views
177
Bondjames
Bondjames
Steve-vfp9user
  • Locked
  • Question
Showing totals on a report for some records based on a condition but when all are requested 1
Replies
5
Views
52
Steve-vfp9user
Steve-vfp9user

Part and Inventory Search

Sponsor

Back
Top