>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.