Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Show at report
Message
 
To
14/09/2004 05:46:17
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Title:
Miscellaneous
Thread ID:
00941826
Message ID:
00943971
Views:
25
Try putting all into a SQL-Select... like below (tested by creating bogus table structure to simulate your tables).
use in ( select( "C_MyAnswer" ) )

select;
	"1" as SortOrder,;
	a.divi,;
	a.Nam,;
	sum( b.NetTot ) AS NetTot,;
	sum( b.Cut5 ) AS Cut5,;
	sum( b.Cut4 ) AS Cut4,;
	sum( b.Cut3 ) AS Cut3,;
	sum( b.Cut2 ) AS Cut2,;
	sum( b.Cut1 ) AS Cut1,;
	sum( b.VFound1 ) AS VFound1,;
	sum( b.VFound2 ) AS VFound2,;
	sum( b.VDisc ) AS VDisc,;
	sum( b.TotalAdd ) AS TotalAdd,;
	sum( b.MSal ) as MSal,;
	sum( b.Thera ) as Thera,;
	sum( b.Suber ) as Suber,;
	sum( b.Tranp ) as Tranp,;
	sum( b.Soci ) as Soci,;
	sum( b.Tax1 ) as Tax1,;
	sum( b.Allad ) as Allad,;
	sum( b.TotExtra ) as TotExtra;
   from;
	AllDivi a,;
	Salar b;
   where;
	a.Divi = b.Dept;
   group by;
	SortOrder,;
	a.Divi,;
	a.Nam;
   into;
        cursor C_MyAnswer;
UNION ALL;
select;
	"2" as SortOrder,;
	padr( "*", len( a.divi )) as Divi,;
	padr( "Report Totals", len( a.Nam )) as Nam,;
	sum( b.NetTot ) AS NetTot,;
	sum( b.Cut5 ) AS Cut5,;
	sum( b.Cut4 ) AS Cut4,;
	sum( b.Cut3 ) AS Cut3,;
	sum( b.Cut2 ) AS Cut2,;
	sum( b.Cut1 ) AS Cut1,;
	sum( b.VFound1 ) AS VFound1,;
	sum( b.VFound2 ) AS VFound2,;
	sum( b.VDisc ) AS VDisc,;
	sum( b.TotalAdd ) AS TotalAdd,;
	sum( b.MSal ) as MSal,;
	sum( b.Thera ) as Thera,;
	sum( b.Suber ) as Suber,;
	sum( b.Tranp ) as Tranp,;
	sum( b.Soci ) as Soci,;
	sum( b.Tax1 ) as Tax1,;
	sum( b.Allad ) as Allad,;
	sum( b.TotExtra ) as TotExtra;
   from;
	AllDivi a,;
	Salar b;
   where;
	a.Divi = b.Dept	


index on sortorder + divi + nam tag MyRptOrder
The first part of the "SQL" will sum each column on a per name/division basis (as per the "group by"). The UNION ALL (that does not have a "group by") will re-sum ALL records, regardless of the department grouping into a single record. I used "1" and "2" as the first field for "SortOrder" so the "1" (by department) records are listed first, then the "2" (report total) record is listed last.

HTH
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform