Solved

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

Posted on 2009-05-04
12
1,186 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:OceanStars
  • 6
  • 5
12 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
You can do all this in single query. Can you provide some sample set and the expected result?
0
 

Author Comment

by:OceanStars
Comment Utility
Thank you. will do.  on the job for next many hours.  sorry for the delay.
0
 

Author Comment

by:OceanStars
Comment Utility
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            
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
messed up your table and column names in my previous post. check this.
1st Query:

------------------------------------------------------
 

SELECT T1.[ID], 

       T1.[SOT], 

       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.[SOT] = T2.[SOT] 

                     AND T2.[QtyPass] > 0 AND T2.[QtyFail] > 0);
 

2nd Query:

-----------------------------------------------------------------------
 

SELECT T1.[ID], 

       T1.[SOT], 

       T1.[QtyPass], 

       T1.[QtyFail]

  FROM Inspection AS T1

 WHERE T1.[QtyFail] <> 0

   AND T1.[SOT] IN (SELECT T2.[SOT]

                             FROM Inspection AS T2

                            WHERE T2.[QtyFail] <> 0

                            GROUP BY T2.[SOT]

                           HAVING SUM(IIF(T2.[QtyPass]=0,0,1)) = 0);
 
 

3rd Query:

-----------------------------------------------------------------------
 

SELECT T1.[ID], 

       T1.[SOT], 

       T1.[QtyPass], 

       T1.[QtyFail]

  FROM Inspection AS T1

 WHERE T1.[QtyPass] <> 0

   AND T1.[SOT] IN (SELECT T2.[SOT]

                             FROM Inspection AS T2

                            WHERE T2.[QtyPass] <> 0

                            GROUP BY T2.[SOT]

                           HAVING SUM(IIF(T2.[QtyFail]=0,0,1)) = 0);

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:OceanStars
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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.
0
 

Author Comment

by:OceanStars
Comment Utility
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.
0
 

Author Closing Comment

by:OceanStars
Comment Utility
Thank you for your quick response time.
0
 

Author Comment

by:OceanStars
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility

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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now