Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 To: Scott Warris
  Where is Scott Warris?
 Lima
 Ohio - United States
 Scott Warris
 Tags
Subject: RE: Total Like Items in a table
Thread ID: 289167 Message ID: 289190 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 29, 2010 6:31:00 AM         
   


> Hello, I've got a table that contains 1000's of records and I need to combine the like records and total the QTY field of the like items.
>
> My table contains the following fields ItemNum, Desc, Qty and Qtytotal
>
> I've thought about just appling an index with the unique command to filter out the like items but I need to store the sum Qty field to the Qtytotal field and I can't see how to do this.
>
> Thanks for your help..Scott

Actually it is called runing total
here are some examples


Example # 1
 Use yourtable
lnTotal = 0
Scan
  Replace fld3 with lnTotal + fld1 + fld2
  lnTotal = fld3
Endscan

Example # 2

Create Cursor tariq (fld1 n(5), fld2 n(5), fld3 n(5))
Insert into tariq (fld1, fld2) values (1,2)
Insert into tariq (fld1, fld2) values (6,8)
Insert into tariq (fld1, fld2) values (3,7)
Insert into tariq (fld1, fld2) values (0,3)
lnTotal = 0
Scan
  Replace fld3 with lnTotal + fld1 + fld2
  lnTotal = fld3
EndScan
browse

Example # 3

REPLACE ALL fld3 WITH (fld1 + fld2)

does not work for you? Or is this a read-only SQL Cursor? If so then you can do it in the SQL like this:

SELECT fld1, fld2, (fld1 + fld2) AS fld3 FROM wherever INTO CURSOR summary

Example # 4

Running totals are generally calculated things but do require a 'sorting' field. ie:

Create cursor myCursor (Fld1 i, Fld2 i)
Insert into myCursor values (1,2)
Insert into myCursor values (6,8)
Insert into myCursor values (3,7)
Insert into myCursor values (0,3)
Select Sys(2015) as sorter,fld1,fld2 ;
from myCursor ;
into cursor crsRT nofilter

Select a.fld1,a.fld2,;
sum(Nvl(b.fld1+b.fld2,0))+a.fld1+a.fld2 as runTot ;
from crsRT a ;
left join crsRT b on a.sorter > b.sorter ;
group by a.Sorter,a.Fld1,a.Fld2

Example # 5

SELECT MyFile 
GO TOP 
myField3 = 0 
SCAN 
MyField3 = MyField3 + (Fld1+Fld2) 
REPLACE Fld3 WITH MyField3 
ENDSCAN 


Hope this will help

ENTIRE THREAD

Total Like Items in a table Posted by Scott Warris @ 12/28/2010 9:50:51 PM
RE: Total Like Items in a table Posted by Anders Altberg @ 12/28/2010 10:05:28 PM
RE: Total Like Items in a table Posted by Scott Warris @ 12/29/2010 3:28:59 PM
RE: Total Like Items in a table Posted by Anders Altberg @ 12/29/2010 5:04:13 PM
RE: Total Like Items in a table Posted by Tariq mehmood @ 12/29/2010 6:31:00 AM
RE: Total Like Items in a table Posted by Jun Tangunan @ 12/29/2010 7:14:22 AM
RE: Total Like Items in a table Posted by Scott Warris @ 12/29/2010 5:15:48 PM