>I have a database that tracks the number of proposals written, the date written, and their amounts.
>I must generate a report that groups each author by billing category, then by year-to-date total
>(the highest total first), and I must also include the author's month-to-date total.
>
>Sample report output:
>
>ID Lastname YTD Amt YTD Cnt MTD Amt MTD Cnt
>Senior Consultant
>054 Smith $68,410 1 $68,410 1
>161 Jones $117,512 3 $15,500 1
>
>Senior Project Manager
>165 Brown $280,115 6 $275,115 5
>180 Davis $391,690 4 $8,735 2
>
>Here is the query:
>
>SELECT Empl_no.billing_grp, Empl_no.lastname, Proposal.author_id,;
> Proposal.prop_amt, Proposal.project_no, Proposal.prop_date,;
> MONTH(Proposal.prop_date);
> FROM projects!empl_no INNER JOIN projects!proposal ;
> ON Empl_no.empl_no = Proposal.author_id;
> WHERE Empl_no.terminated = .F.;
> AND Proposal.prop_date >= {01/01/1997};
> AND YEAR( Proposal.prop_date) <= YEAR(DATE());
> AND MONTH( Proposal.prop_date) <= MONTH(GOMONTH(DATE(),-1));
> ORDER BY Empl_no.billing_grp, Proposal.author_id, 7,;
> Proposal.prop_amt DESC;
> INTO CURSOR Proprank
>REPORT FORM c:\databases\marketing\report\proprank.frx PREVIEW
>
>This query provides all the information I need for the report; however, I can only group the data
>by the employee's ID if I want the YTD and MTD totals. If I use the query to order the data by
>SUM(prop_amt) and group by employee ID, then I can't extract the MTD totals.
>
>Can someone help me? I'm at my wits end!

The basic approach to your problem, is to create two more cursors: one with ID and MTD SUM, another with ID and YTD SUM. Then you link main cursor with these two (by ID) and group by three fields.