>Hi Cetin,
>
>I was experimenting on using my old rejects.dbf table and trying to append new data with the original table. I guess when I appended my data, it appended all of it including the existing ones already. I thought that it would only append the ones that are not in my table. To fix this, I created a new table again and appended the original rejects.dbf to my new table in my program. The problem was solved after this.
>
>Thanks,
>Sam
Sam,
It's hard to understand the whole process and w/o seeing real data you know... I at least was not sure if m.vendor stands for vendorID or vendorName.
StartDate = ctod(THISFORM.txtStartDate.Value)
EndDate = ctod(THISFORM.txtEndDate.Value)
Vendor = THISFORM.txtVendorID.Value
Tolerance = val(THISFORM.txtTolerance.Value)
LOCAL lcWhereExt
lcWhereExt = IIF( EMPTY(m.Vendor), '', ' and oRej.VendorID == m.Vendor' )
SELECT oRej.vendorID, ;
SUM( IIF(weekdays(oRej.daterecv,oMst.curdue_10) < m.Tolerance,1,0) ) as Ot ;
FROM Rejects oRej ;
inner JOIN OrderMaster oMst ;
ON oMSt.ORDNUM_10 == oRej.OrderNum AND ;
oMst.LINNUM_10 == oRej.LineNum and ;
oMst.DELNUM_10 == oRej.DelNum ;
WHERE oRej.daterecv between m.StartDate and m.EndDate &lcWhereExt ;
GROUP BY 1 ;
INTO CURSOR crsOt
SELECT ;
orej.vendorID, ;
vendorname, ;
cnt(*) as NumRect, ;
sum(qtyrecv) as RecvQty, ;
sum(qtyrej) as RejQty, ;
Round(100*SUM(qtyrej)/SUM(qtyrecv),2) as PerRej, ;
ot.Ot as OnTime ;
FROM rejects orej ;
LEFT JOIN crsOt ot ON orej.venDorID == ot.vendorID ;
GROUP BY 1,2,7 ;
WHERE oRej.daterecv between m.StartDate and m.EndDate &lcWhereExt ;
INTO CURSOR VendorPerf ;
nofilter
BROWSE
if reccount() = 0
MessageBox("No Records to Display!",64)
else
VpStartDate = m.StartDate
VpEndDate = m.EndDate
VpTolerance = m.Tolerance
REPORT FORM VendorPerformance PREVIEW
endif
Code is VFP6 compatible and ready for VFP9 (that's why I have group by 1,2,7 instead of just 1 there IOW). Hope I got it somewhat right.
PS: I didn't bother ot/numrect*100 (and actually wouldn't for PerRej here) because it could be shown on report as a calculation.
Cetin