Challenge: Have a table called Inspection with .5 million records. The focus is on three main fields of interest. SOT(Sales Order), PASS QTY or FAIL QTY.
Some records will only have a PASS or FAIL value, some will have both values.
There are multiple records with the same SOT #.
Mission: Final selection of records must have:
1. No duplicate SOT#s.
2. Must choose records with the first occurrence of a FAIL QTY first!
3. Then choose records with the first occurence of a PASS QTY first and
where FAIL QTY is = 0 or is null
AND the SOT# does not exist in the first selection of records with a FAIL QTY.
Solution: I have a table called dwt_QtyFailFirst_pareto_Ga
tes_3_5 which lists all the records with FAIL QTY. Now I am trying to complete the selection with step 3 above with the code below, but the code never completes running and I am sure is a HUGE performance hit.
Any ideas on how to go through half a million records and compare it against another table or query and say don't choose any of the SOTs listed in the 2nd query and get the query to actually complete running?
SELECT Min(Inspection.ID) AS MinOfID, Inspection.SOT INTO dwt_ParetoQtyPassNoFail
WHERE (((nz([QtyPass],0))>0) AND ((Inspection.When)>=#1/1/2008#) AND ((Inspection.QtyFail) Is Null Or (Inspection.QtyFail)=0) AND ((Inspection.DetailCatID) Is Null) AND
((Inspection.SOT) Not In (Select dwt_QtyFailFirst_Pareto_Gates_3_5.SOT from dwt_QtyFailFirst_Pareto_Gates_3_5)))
GROUP BY Inspection.SOT, Inspection.InspectionOperationID, "Pass No Fail", Inspection.PartStatusID
HAVING (((Inspection.InspectionOperationID)=2 Or (Inspection.InspectionOperationID)=3) AND ((Inspection.PartStatusID)=1));