?
Solved

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

Posted on 2009-05-04
12
Medium Priority
?
1,192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 41

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 41

Accepted Solution

by:
Sharath earned 2000 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 41

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 41

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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