asked on

Access 2003 NOT IN (select subquery) doesn't work

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_Gates_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
FROM Inspection
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));

Open in new window

Hamed Nasr

Prepare a version of the database with 10 records, and a query that uses table fields without expressions or grouping.
If it works as you want then start using the expressions and grouping in the query.
Otherwise attach this this version to have a look at.

Sharath S

You can do all this in single query. Can you provide some sample set and the expected result?

OceanStars

ASKER

Thank you. will do. on the job for next many hours. sorry for the delay.

OceanStars

ASKER

Thank you for your comments.

Here is a simplified sample of what the final results should be. This should be so simple. I don't understand why its failing.
SELECT Inspection.SOT, Inspection.QtyPass, Inspection.QtyFail
FROM Inspection
WHERE (((Inspection.QtyPass)=0) AND ((Inspection.QtyFail)>0) AND ((Inspection.SOT) Not In (Select SOT from Fail_and_Pass)));

When I try the above query the system never completes almost as if it were in a continuous loop.

Here is a simplified version.
Inspection table:
# records meeting these conditions
3,080 Both QtyPass and QtyFail >0
29,264 QtyFail > 0 and QtyPass = 0
227,168 QtyPass > 0 and QtyFail = 0

Table: Inspection > 250,000 records
ID      SOT      QtyPass      QtyFail
1      A      1      0
2      A      0      1
3      A      2      1
4      B      1      0
5      B      0      1
6      C      2      3
7      C      1      0
8      C      0      1

1st Query: Fail_and_Pass (QtyFail and QtyPass both are > 0)
ID      SOT      QtyPass      QtyFail
3      A      2      1
6      C      2      3

2nd Query: Fail_and_NoPass_SOT_not_in_1st_Query
ID      SOT      QtyPass      QtyFail
5      B      0      1

3rd Query: Pass_and_NoFail_SOT_not_in_1st_Query
ID      SOT      QtyPass      QtyFail
4      B      1      0

Sharath S

try this

1st Query:
------------------------------------------------------
 
SELECT T1.[ID], 
       T1.[Inspection], 
       T1.[QtyPass], 
       T1.[QtyFail]
  FROM Inspection AS T1
 WHERE T1.[QtyPass] > 0 AND T1.[QtyFail] > 0
   AND T1.[ID] = (SELECT MIN(T2.[ID]) 
                    FROM Inspection AS T2 
                   WHERE T1.[Inspection] = T2.[Inspection] 
                     AND T2.[QtyPass] > 0 AND T2.[QtyFail] > 0);
 
2nd Query:
-----------------------------------------------------------------------
 
SELECT T1.[ID], 
       T1.[Inspection], 
       T1.[QtyPass], 
       T1.[QtyFail]
  FROM Inspection AS T1
 WHERE T1.[QtyFail] <> 0
   AND T1.[Inspection] IN (SELECT T2.[Inspection]
                             FROM Inspection AS T2
                            WHERE T2.[QtyFail] <> 0
                            GROUP BY T2.[Inspection]
                           HAVING SUM(IIF(T2.[QtyPass]=0,0,1)) = 0);
 
 
3rd Query:
-----------------------------------------------------------------------
 
SELECT T1.[ID], 
       T1.[Inspection], 
       T1.[QtyPass], 
       T1.[QtyFail]
  FROM Inspection AS T1
 WHERE T1.[QtyPass] <> 0
   AND T1.[Inspection] IN (SELECT T2.[Inspection]
                             FROM Inspection AS T2
                            WHERE T2.[QtyPass] <> 0
                            GROUP BY T2.[Inspection]
                           HAVING SUM(IIF(T2.[QtyFail]=0,0,1)) = 0);

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OceanStars

ASKER

I appreciate your efforts. Work is seriously slamming me. The code didn't solve the issue. Let me get back with this in a day or two. Then I will show you what I have partially done. Still not solved yet. Appreciate your patience.

Sharath S

I tested those queries on the sample data you have provided and working fine. Provide some more sample set where exactly you are facing issue so that it would be easy to fix the queries if needed.

OceanStars

ASKER

Sharath,
Thank you for your time. I will go ahead and award the points to you. I have no doubt your queries worked for you. I think the problem is related to the large amount of data I have and a bad network connection. Here is the solution I was finally able to get to work. I had to make a table for the Fail Qtys, Then with a second query to find the Pass Qtys while the SOT was NOT IN the Fail table. Then I combined the results of both into another table. My system would never complete a query off of another query. I had to turn it into a table.
First query:
SELECT Min(Inspection.ID) AS MinOfID, Inspection.SOT INTO dwt_ParetoFail
FROM Inspection
WHERE (((Inspection.InspectionOperationID)=2 Or (Inspection.InspectionOperationID)=3) AND ((Inspection.QtyFail)>0) AND ((Inspection.PartStatusID)=1) AND ((Inspection.When)>=#1/1/2008#))
GROUP BY Inspection.SOT;

Second query:
SELECT Min(Inspection.ID) AS MinOfID, Inspection.SOT INTO dwt_ParetoPass
FROM Inspection
WHERE (((Inspection.InspectionOperationID)=2 Or (Inspection.InspectionOperationID)=3) AND ((Inspection.QtyPass)>0) AND ((Inspection.PartStatusID)=1) AND ((Inspection.When)>=#1/1/2008#) AND ((Inspection.SOT) Not In (Select [dwt_ParetoFail].[SOT] from dwt_ParetoFail)))
GROUP BY Inspection.SOT
ORDER BY Inspection.SOT;

The database has been what I believe is corrupted and recreated so many times now. I've been in this field for 20 years and in 20 years combined have never had the database problems I've had at this workstation. While data is processing there will be a network disconnect without a reconnect. My queries, which are many, now give compile errors, so I can't even test many of them. And I am talking about the simplest of queries just pulling straight fields from a table. I am having to start over with a clean database and recreate as much as I can. Even when I import a good query from a backup, I still get the compile errors. To top it off, our company is restructuring and these past two weeks eliminated a lot of positions. Today is my last day at work. I will take the database off the network and only work off the network.

Again, thank you for your patience. Happy Friday.

OceanStars

ASKER

Thank you for your quick response time.

OceanStars

ASKER

I have moved the database to a non-networked pc. Pentium 4 CPU 3.20 GHz 1.00 GB ram.
I was able to process queries like yours and mine.. WITHOUT making a table.. just pulling from query to query and the process completed in less than 2 seconds flat!! It was definitely a network issue! Thank you again Sharath.

Sharath S

OceanStars - thanks for the long post and the points. Definitely that was a network issue.
Ohh..you have 20 long years of experience in the industry. thats great.
>> Today is my last day at work.
Are you moving to othe company? Anyway all the best and have a nice weekend.