Thanks for the advice David.
This is what I finally ended up doing, and it seems to work fine:
***Form.init
if thisform.r_noload
return .f.
ELSE
select ;
cb.date_serv, ;
sum(iif(cb.type_entry="DEP",cb.dep_dis,0000000.00)) as
deposit, ;
sum(iif(cb.type_entry="DIS",cb.dep_dis,0000000.00)) as
disburse, ;
(sum(iif(cb.type_entry="DEP",cb.dep_dis,0000000.00)) -
sum(iif(cb.type_entry="DIS",cb.dep_dis,0000000.00))) as bal, ;
000000000.00 as balance, ;
journal ;
from ;
cashbal cb ;
where ;
cb.COMP_ID = 'SHS' ;
group by ;
cb.pk_cash, cb.date_serv, cb.dep_dis ;
order by ;
cb.date_serv into cursor cb1 readwrite
****************************************************************************
**********************************
select cb1
mbal = 0
scan
if recno() = 1
replace balance with deposit - disburse
mcbal = balance
else
replace balance with bal + mcbal
mcbal = balance
endif
endscan
with thisform.Hspageframe1.Page1.hscntsearch.Hsgrid1
.recordsource = "cb1"
.height = 500
.width = 500
.columncount = 5
.fontsize = 9
.top = 50
.left = 5
.deletemark = .f.
.readonly = .t.
.Column1.controlsource = "cb1.date_serv"
.Column1.width = 65
.Column1.Header1.caption = "Date"
.Column2.controlsource = "transform(cb1.deposit,'999,999,999.99')"
.Column2.width = 100
.Column2.Header1.caption = "Deposit"
.Column3.controlsource = "transform(cb1.disburse,'999,999,999.99')"
.Column3.width = 100
.Column3.Header1.caption = "Disbursements"
.Column4.controlsource = "transform(cb1.balance,'999,999,999.99')"
.Column4.width = 100
.Column4.Header1.caption = "Balance"
.Column5.controlsource = "RTRIM(cb1.journal)"
.Column5.width = 100
.Column5.Header1.caption = "Notes"
.Column5.ALIGNMENT = 3
endwith
GO top
endif
James E Harvey
Hanover Shoe Farms, Inc.
M.I.S./Corresponding Officer
Off: 717-637-8931
fax: 717-637-6766
email: [EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Dave Crozier
Sent: Friday, February 23, 2007 11:51 AM
To: 'ProFox Email List'
Subject: RE: Running balance
James,
This might help to give you the right idea:
Set strictdate to 0
Set Date to mdy
Create Cursor trs(id i, orgamt y,payment y, date d)
Insert into Trs values (1,250.00, 10.00, {01/01/01})
Insert into Trs values (1,250.00, 15.00, {02/01/01})
Insert into Trs values (1,250.00, 10.00, {03/01/01} )
cWho=1
Select all ;
T1.id, ;
T1.orgamt, ;
T1.payment, ;
T1.orgamt - sum(T2.payment) as balance;
from Trs T1 ;
join Trs T2 on T1.id=T2.id ;
and T1.date>=T2.date ;
group by T1.id,T1.orgamt, T1.date ;
where T1.id=?cwho ;
Into Cursor curTemp
Browse
Dave Crozier
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of James E Harvey
Sent: 23 February 2007 16:38
To: 'ProFox Email List'
Subject: Running balance
We want to create a grid that "simulates" the look of an Excel file that has
a column that is a "running" total of two other columns.
I can't make this work in a grid using the table itself, so I'm trying to
create a cursor to use as the data source of the grid.
Table structure is:
Pk_cash auto increment primary key
Date_serv date
Comp_id c 3
Dep_dis n 11 2
Type_entry c 3
Sample data:
1
02/01/2007
SHS
1,000.00
DEP
2
02/01/2007
SHS
500.00
DIS
3
02/02/2007
SHS
2,000.00
DEP
4
02/03/2007
SHS
200.00
DIS
Results desired in the grid:
Date Deposits Disbursements Balance
02/01/2007 1,000.00 1,000.00
02/01/2007 500.00 500.00
02/02/2007 2,000.00 2,500.00
02/03/2007 200.00 2,300.00
The grid is read only, data will be added, edited in typical form with text
boxes.
The select below is missing the "running balance" column, and nothing I've
tried has worked.
SELECT cb.pk_cash, cb.date_serv,
SUM(IIF(cb.type_entry="DEP",cb.dep_dis,0000000.00)) as deposit,
SUM(IIF(cb.type_entry="DIS",cb.dep_dis,0000000.00)) as disburse FROM
cashbal cb WHERE cb.COMP_ID = 'SHS' GROUP BY cb.pk_cash, cb.date_serv ORDER
BY cb.date_serv
James E Harvey
Hanover Shoe Farms, Inc.
M.I.S./Corresponding Officer
Off: 717-637-8931
fax: 717-637-6766
email: [EMAIL PROTECTED]
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
