Solved

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

Posted on 2009-05-04
12
1,188 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
ID: 24301854
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
ID: 24302003
You can do all this in single query. Can you provide some sample set and the expected result?
0
 

Author Comment

by:OceanStars
ID: 24310926
Thank you. will do.  on the job for next many hours.  sorry for the delay.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:OceanStars
ID: 24311924
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
ID: 24312232
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
ID: 24312248
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
 

Author Comment

by:OceanStars
ID: 24329151
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
ID: 24331293
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
ID: 24335919
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
ID: 31577873
Thank you for your quick response time.
0
 

Author Comment

by:OceanStars
ID: 24337580
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
ID: 24338621

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

776 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