General information
Category:
Reports & Report designer
Title:
Query/Report Problems
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!
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only