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!