Re: Procedures by Anders
Anders
Sat Oct 23 07:53:15 CDT 2004
It looks like you didin't include the GROUP BY clause. If you don't you get
the sum for the table
GROUP BY ref,Tenants.title, Tenants.firstname, Tenants.lastname, ;
debit,credit,balance
You GROUP BY the same columns you have in the SELECT list.
You can also run an extra query before launching the report.
SELECT ref, SUM(debit-credit) AS marrears FROM Table1 JOIN Table2 ON ... ;
GROUP BY ref INTO CURSOR Temp
In the report you pick up this sum in a textbox with the expression
LOOKUP(Temp.marrears, Reporttable.ref, temp.ref )
This way you can place the sum anywhere in any part of the report band, the
header, the footer or every line.
-Anders
"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
news:xT1Jy5CiNVeBFw4i@tjhobson.demon.co.uk...
> I think I have solved it.
>
> Instead of trying to put the function in the SQL statement I have dealt
> with it by using the SUM function in the report editor. I then put
> everything in a
> group under tenants.name instead of in the detail section of the report.
>
> Although I still do not understand why the SQL statement did not work !.
>
> Tim
>
> In article <WVEgjxAmITeBFw95@tjhobson.demon.co.uk>, Tim Hobson
> <Tim@tjhobson.demon.co.uk> writes
> >Rick,
> >My original query was how to use this sort of procedure in a SQL
> >statement calling a report form. I find your suggestion OK as a
> >procedure called from a form, but still cannot make it work properly
> >for a report.
> >
> >I give below sample coding used for a test prg. This works as is, but
> >if I add "SUM(debit-credit) AS marrears" to the SELECT statement it
> >only shows a single tenant record instead of the lot. Where I I going
> >wrong ?
> >
> >** Testsql
> >use tenants in 1
> >use tenacc in 2
> >sele tenants
> >SELECT ref,Tenants.title, Tenants.firstname, Tenants.lastname,
> >debit,credit,balance ;
> > FROM garages!tenants INNER JOIN garages!tenacc ;
> > ON Tenants.tenantref = Tenacc.tenantref;
> > WHERE substr(Tenacc.ref,1,2) $ ('RE DU');
> > ORDER BY Tenants.lastname;
> > INTO CURSOR Testsql
> >REPORT FORM testsql.frx NOCONSOLE PREVIEW
> >
> >select tenants
> >close data
> >cancel
> >
> >Tim
> >
> >
> >In article <uv3zc$stEHA.2808@TK2MSFTNGP14.phx.gbl>, Rick Bean
> ><rgbean@unrealmelange-inc.com> writes
> >>Tim,
> >>First you need to examine your function code - a single SQL SELECT
> >>could replace most of this function's code:
> >> ** Totals rents due for a tenant
> >>LOCAL mtot
> >>SELECT
> >>SUM(rentdue+vatdue+servicedue-rentrec-vatrec-servicerec+writeoff+vatwri
> >>teoff) AS calcTot ;
> >> FROM tenacc ;
> >> INTO cursor mytemp ;
> >> WHERE tenacc.tenant = tenants.tenantref
> >>mtot = mytemp.calcTot
> >>USE IN SELECT("mytemp")
> >>RETURN mtot
> >>* EOF rentarrears
> >>
> >>This will be substantially faster - it only needs to process the
> >>records once instead of eight times!
> >>
> >>I'm not understanding the how you are using a function like this in
> >>SQL, although if you were using VFP 9.0, it would be trivial to use
> >>this sub-query in another SQL statement!
> >>
> >>Rick
> >>
> >>"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
> >>news:QulR9pBuYodBFwXV@tjhobson.demon.co.uk...
> >>>I keep several functions in a non visual class which I call up when I
> >>> need them on a form by using, for example,
> >>> thisform.txtarrears.value=oPropApp.rentarrears(). This works well, and
> >>> avoids having a separate procedure file.
> >>>
> >>> My problem is that I want to use this in a report and SQL does not
seem
> >>> to recognise functions called from a class. Is there any way around
this
> >>> ?
> >>>
> >>> I use VFP6.
> >>>
> >>> An example of such a function is below.
> >>>
> >>> ** Totals rents due for a tenant
> >>> mdbf=ALIAS()
> >>> mref=tenants.tenantref
> >>> SELECT tenacc
> >>> SUM(rentrec) TO mtot1 FOR tenacc.tenant=mref
> >>> SUM(rentdue) TO mtot2 FOR tenacc.tenant=mref
> >>> SUM(vatrec) TO mtot3 FOR tenacc.tenant=mref
> >>> SUM(vatdue) TO mtot4 FOR tenacc.tenant=mref
> >>> SUM(servicerec) TO mtot5 FOR tenacc.tenant=mref
> >>> SUM(servicedue) TO mtot6 FOR tenacc.tenant=mref
> >>> SUM (writeoff) TO mtot7 for tenacc.tenant=mref
> >>> SUM (vatwriteoff) TO mtot8 for tenacc.tenant=mref
> >>> mtot=mtot2+mtot4+mtot6-mtot1-mtot3-mtot5+mtot7+mtot8
> >>> SELECT (mdbf)
> >>> LOCATE FOR tenants.tenantref=mref
> >>> RETURN mtot
> >>> * EOF rentarrears
> >>> ********************************************************
> >>>
> >>> --
> >>> Tim Hobson
> >
>
> --
> Tim Hobson