>I have this free table and I am trying to calculate the % Rejected per manufacturer. I am trying to create a report where it will give me a summary per manufacturer per year. I have the vendor ID, Vendor Name, Number of Receipts, Quantity Received, Quantity Rejected, Percent Rejected and On Time fields in it. Somehow, my code does not properly calculate the Percent Rejected per vendor, but everything else is okay. Oh, I still need to work on my OnTime field for this. Please advice...
>
>

>* get the orders from free table - rejects.dbf
>SELECT * FROM rejects ;
>WHERE daterecv BETWEEN StartDate AND EndDate ;
>ORDER BY vendorname, daterecv ;
>INTO CURSOR OrderRejects
>
>* create a VendorPerfomance table to hold the report values
>CREATE CURSOR VendorPerf(VendorID c(5), VenName c(25), NumReceipts I, QtyRecv N(10), QtyRej N(6), PerRej N(4,2), OnTime I)
>
>SELECT OrderRejects
>
>DO WHILE !eof()
>  Vendor = OrderRejects.vendorname
>  VenID = OrderRejects.VendorID
>  NumRect = 0
>  RecvQty = 0
>  RejQty = 0
>  OT = 0
>  DO WHILE Vendor = OrderRejects.vendorname
>    NumRect = NumRect + 1
>    RecvQty = OrderRejects.qtyrecv + RecvQty
>    RejQty = OrderRejects.qtyrej + RejQty
>    PerRej = Round((RejQty/RecvQty)*100,2)    && need help here...
>    SELECT OrderMaster
>    SET KEY TO OrderRejects.OrderNum
>    LOCATE FOR LINNUM_10 = OrderRejects.LineNum and ;
>	DELNUM_10 = OrderRejects.DelNum		
>    SELECT OrderRejects
>    SKIP
>    ENDDO
>    INSERT INTO VendorPerf(VendorID,VenName,NumReceipts,QtyRecv,QtyRej,PerRej,OnTime) ;
>	VALUES (VenID,Vendor,NumRect,RecvQty,RejQty,PerRej,OT)
>ENDDO
>

>
>Thanks,
>Sam

Hi Sam,
Welcome to UT:) (soecify your version or else you'd get VFP9 specific SQL mostly - I know you've VFP7).

Your code sound to translate:

* create a VendorPerfomance table to hold the report values
CREATE CURSOR VendorPerf (VendorID c(5), VenName c(25), NumReceipts I, QtyRecv N(10), QtyRej N(6), PerRej N(4,2), OnTime I)

SELECT ;
 vendorname,;
 vendorID,;
 cnt(*) as numRect, ;
 sum(qtyrej) as rejQty, ;
 sum(qtyrecv) as recvQty ;
 FROM rejects ;
 WHERE daterecv BETWEEN StartDate AND EndDate ;
 GROUP BY 1,2 ;
 ORDER BY 1 ;
 INTO CURSOR OrderRejects

scan 
INSERT INTO VendorPerf ;
(VendorID,VenName,NumReceipts,QtyRecv,QtyRej,PerRej,OnTime) ;
VALUES ;
(OrderRejects.VendorID,;
 OrderRejects.VendorName,;
 OrderRejects.NumRect,;
 OrderRejects.RecvQty,;
 OrderRejects.RejQty,;
 ROUND(OrderRejects.RejQty/OrderRejects.RecvQty*100,2),0)
endscan

or:

SELECT ;
 vendorID as VenID,;
 vendorname,;
 cnt(*) as numReceipts, ;
 sum(qtyrej) as QtyRej, ;
 sum(qtyrecv) as QtyRecv ;
 FROM rejects ;
 WHERE daterecv BETWEEN StartDate AND EndDate ;
 GROUP BY 1,2 ;
 ORDER BY 1 ;
 INTO CURSOR OrderRejects

SELECT *, ;
 ROUND(QtyRej/QtyRecv*100,2) as PerRej,;
 0 as Ontime ;
 FROM OrderRejects ;
 INTO CURSOR VendorPerf

However VendorPerf cursor might never be needed for a report and calculation could be done directly on report.
Cetin