I am a relatively new SQL user, trying to manage an MRP system for a manufacturing company. I have signed up for a couple of Access and SQL classes through Udemy but have not completed them yet. I am hoping to get some help on a specific issue to keep me moving forward in fixing our mismanaged database. I'm not up to speed on SQL terminology so I'm not even sure if my topic makes sense. Anyway, I have a table "OrderDet" that contains various information about customer orders including,
JobNo, PartNo, ProdCode, PartDesc, Status, MasterJobNo, Etc
The product that we sell to our customer are considered ProdCode = 'CUST PART'. Customer parts can be made up of sub-assemblies ("SUB ASSEM") or sub-components ("SUB COMP"). All CUST PART's, SUB ASSEM's and SUB COMP's have their own JobNo in the table. All SUB ASSEM's and SUB COMP's are tied to their CUST PART using the MasterJobNo field. When any JobNo is complete, its status changes from OPEN to CLOSED. For years the system was used improperly and the SUB parts were not getting closed when they were done. As a result, our "Open Order Report" shows several open jobs that are actually done and gone, creating confusion for the manufacturing managers. I am trying to create a Query that finds all SUB COMP's and SUB ASSEM's that are open where the MasterJobNo Job is closed. Conceptually this is an easy thing to accomplish. Its the verification of the MasterJobNo 'Status' that I don't know how to do. Below is the simple Query that gives me the first step, but I don't know how to tie in the MasterJobNo "Status" to eliminate all the jobs where the SUB COMP is Open and the CUST PART (MasterJobNo) is still Open.
SELECT *
FROM OrderDet
WHERE ProdCode = 'SUB COMP'
AND Status = 'OPEN'
ORDER BY JobNo
Do I need to perform a nested SELECT statement and somehow pass the MasterJobNo from the first Query into the second? Any help here would be appreciated, and sorry if this is tough to read. Hopefully the SQL courses will help with the terminology.