Cindy,

I think I understand the UNION but not sure how the records are combined into one cursor.... that and I wish my SELECTS were THAT simple.... here is what I have right now....

Select client.clientnum, client.clientname, 0.0 as begbal,;
code.codetype,AllTrim(str(code.codenum))+" "+code.codedesc as code, ;
Trim(employee.lastname)+", "+employee.firstname as employee, ;
log.logdate, ;
log.time, ;
log.rate, ;
iif(code.codetype="Time" or code.codetype="Administrative",log.time*log.rate,log.rate) as amount ;
from Timepro!log left join timepro!employee on log.employeeid=employee.employeeid ;
left join timepro!client on log.clientid=client.clientid ;
left join timepro!code on log.codeid=code.codeid ;
where log.firmid=gFirmid and log.logdate<=gEnddate and log.employeeid<>0 and log.clientid<>0 ;
into cursor curswip ;
order by client.clientname, code.codenum,employee


Here is the sum statement I need to add for BEGBAL

sum(iif(code.codetype="Time" and log.logdate iif((code.codetype="Expense" or code.codetype="WIP") and ;
log.logdate iif(code.codetype=="Billings" and ;
log.logdate

What I need for the end result is all the detail (log), sorted by clientnum and if their is a beginning balance, that amount to be listed at least on the first record (if not all records) for that client so that the report GROUPING section can print it before the detail......

Mr Smith
Code 103 09/27/99 $50.00
Code 105 09/27/99 $10.00
Total.......... $60.00

Mr Jones Beg Bal $15.00
Code 99 09/21/99 $10.00
Total.......... $25.00


Any suggestions?





>>I have a select statement that I use to build a report. It reads records in several files based on a main files entrydate field. Detail is listed for everything from a start to an end date.
>>
>>I need to also summarize (beg bal) everything PRIOR to the start date and that needs to be listed in the client header as a BEGINNING BALANCE.
>>
>>I have the main detail select statement working well. When I add a SUM for records prior to the start date and store that in a field, my 50+ records turns into 1 record. The sum seems to lose all the detail.
>>
>>Any suggestions on how to do this? Do I have to somehow run a seperate select statement to get the beginning balances than combine the two into one cursor for the report? Am I overlooking something (most likely) that would correct this problem?
>>
>>Thanks...
>
>Peter,
>
>I think you need a type field in your report cursor, where the types are BeginningBalance, and CurrentDetail. Then you can populate your cursor with the following:
>
>

>SELECT [BeginningBalance] AS cType, ;
>    SUM(yCost) AS yCost, ;
>    dBeginningOfPeriod - 1 AS dDateField ;
>    FROM MasterTable ;
>    Where dDateField < dBeginningOfPeriod ;
>UNION ALL ;
>    SELECT [CurrentDetail] AS cType, ;
>    yCost, ;
>    dDateField ;
>    FROM MasterTable
>    WHERE BETWEEN(dDateField, dBeginningOfPeriod, dEndOfPeriod) ;
>    ORDER BY 3    && dDateField in 3rd position
>

>
>Note that SUM adds everything in the group, in this case all records which meet the WHERE clause in the first part. Please read about UNION to see if you need the "ALL". Notice that in a UNION query the ORDER columns can only be referred to by number.

Peter Brama
West Pointe Enterprises

VFP is getting easier but STILL alot to learn!!