Solved

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

Posted on 2009-05-04
12
1,187 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
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 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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

17 Experts available now in Live!

Get 1:1 Help Now