The following code works well to produce a LIST of combined items from
several tables.  But it would be much more useful if I could include
TOTALS of the number of records with a common SVCNUM or with CODE1 equal
to values 1,2,3.

I would like to be able to show both the list and the totals in the same
report if possible.  Totals either at the end or in the middle somewhere.

TIA
Jerry


  SELECT Payor.Acct,
         Payor.Lastname,
         Payor.Insco,
         Insco.InsTblNum,
         Insco.Name,
         Tcode.T_posted,
         Tcode.T_tcode,
         Services.SvcNum,
         Services.Code1
    FROM "C:\z24\GAIL24\pbsw24\data\Payor.DAT" Payor
         INNER JOIN "C:\z24\GAIL24\pbsw24\data\Insco.DAT" Insco
                 ON Payor.Insco = Insco.InsTblNum
         INNER JOIN "C:\z24\GAIL24\pbsw24\data\patient.dat" Patient
                 ON Patient.Acct = Payor.Acct
         INNER JOIN "C:\z24\GAIL24\pbsw24\data\Tcode.DAT" Tcode
                 ON Patient.Acct = Tcode.T_acctnum
         INNER JOIN "C:\z24\GAIL24\pbsw24\data\Services.DAT" Services
                 ON Tcode.T_tcode = Services.SvcNum
   WHERE (((Insco.InsTblNum = 1)
     AND (Tcode.T_posted > "01/01/2007")
     AND (Tcode.T_tcode < 30000)))
ORDER BY Code1 ASC

"Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
news:8575A3F4-467E-4E89-AE80-CD75A3137476@news.elevatesoft.com...
>
> I would like to be able to show both the list and the totals in the same
> report if possible.  Totals either at the end or in the middle somewhere.
>

If what you're looking for is to print subtotals, any report writer should
let you do that. Even the venerable and much maligned QR.

Robert

Robert wrote:

>
> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
> news:8575A3F4-467E-4E89-AE80-CD75A3137476@news.elevatesoft.com...
> >
> > I would like to be able to show both the list and the totals in the
> > same  report if possible.  Totals either at the end or in the
> > middle somewhere.
> >
>
> If what you're looking for is to print subtotals, any report writer
> should let you do that. Even the venerable and much maligned QR.
>
> Robert

Jerry

I agree with Robert about using the report writer to generate the
totals (which will also help with formating the report for new pages on
totalling etc.), but if REALLY want to get subtotals in the query, then
you can use a UNION.  What you do is create another SELECT that does
the totalling which has dummy columns to match the columns of the first
SELECT and engineer values in the sorted columns to make the subtotals
appear in the right place.  Then just UNION the two (or more) SELECTS
together.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com