Dennis,
Another option would be to create a view:
CREATE VIEW vwHighDollar (ProductNo, Description, ExtPrice) +
AS SELECT +
t1.pr_nbr, t2.pr_des, sum(t1.tr_unitp * t1.tr_qty) +
>FROM usage t1, parts t2 +
>WHE (t1.tr_code = "ox") +
>AND (t1.tr_date between "1/1/01" AND "9/19/01" ) +
>AND (t1.pr_nbr = t2.pr_nbr) +
>GROUP BY t1.pr_nbr, t2.pr_des +
>HAVING SUM (t1.tr_unitp * t1.tr_qty) > 5000 +
>ORDER BY ExtPrice DESC
Examine the data with the command:
EDIT ALL FROM vwHighDollar
OR use the view as the basis for your report.
Randy Peterson
Dennis Fleming wrote:
> Randy,
>
> That works so well, I'll make it into a standard report with headings and
> everything! Thanks.
>
> Dennis
> *****
>
> At 02:17 PM 9/26/01 -0400, you wrote:
> >Dennis,
> >
> > I think you can get your sort fairly easily. Try:
> >
> >EDI t1.pr_nbr, t2.pr_des, sum(t1.tr_unitp * t1.tr_qty) AS ExtPrice +
> >FROM usage t1, parts t2 +
> >WHE (t1.tr_code = "ox") +
> >AND (t1.tr_date between "1/1/01" AND "9/19/01" ) +
> >AND (t1.pr_nbr = t2.pr_nbr) +
> >GROUP BY t1.pr_nbr, t2.pr_des +
> >HAVING SUM (t1.tr_unitp * t1.tr_qty) > 5000 +
> >ORDER BY ExtPrice DESC
> >
> >IF the last line doesn't work, try ORDER BY 3 DESC
> >(3 is the third field after EDIT)
> >
> > Randy Peterson
> >
> >
> >
> >
> >
> Dennis Fleming
> IISCO
> http://www.TheBestCMMS.com
