Tim,
What is your business rule?
A Decision is made by 1 and only 1 decision maker (personnel with DecisionMaker = 1)
Perhaps you could give readers a few examples.
I'm not following the yes/no/maybe in your database???
As has been said, not sure a subquery is needed.
Hi again All -
The business rule is that yes - at the end of the day - one and only one person makes the decision.
OK.....so here goes.... (please see the attached screenshot)
We roll painted steel through rolling mills. Most of the time everything goes great but when it doesn't go well the line between acceptable and rejectable gets really blurry. Depending on what the issue is, the decision can be made by one of several people in upper management to override the QC department's (me and a couple of other guys) decision to reject product and stop the production line. In fact, we in QC override rejectable conditions sometimes if I know with certainty that function is more important to the customer than aesthetic perfection. We don't make those decisions lightly but they do happen from time to time.
In the database I have tables for Jobs, Products, Workstations (rolling mills - which is important).
There are of course tables for various types of inspections. One of those tables is tblLineStop - which is utilized when the decision is made to stop a production line for any length of time while we decide how to remedy the situation.
tblInspectionEvent is the junction table that ties all of this together.
I decided to have all personnel - labor and management in one table ("LUtblPersonnel") with a field ("Role") that allows me to filter whether any given person is an inspector, welder, fabricator, admin, etc. Note: This IS NOT a company wide db and is not tied to the company's main ERP. This db is strictly within the confines of the QC department and in no way interacts with the main business system. And it never will.
You will notice in the attached screenshot that LUtblPersonnel is not "linked" to any other tables because I have assumed it would be poor design to link it to tblInspectionEvent where it used to populate both the "Inspector_FK" field and the "Operator_FK" field. So my naming convention in tblInspectionEvent is a bit misleading. I probably should have called each of those fields "Inspector_ID" and "Operator_ID" respectively but I did not - but I get it and it works.
What is important to note that anywhere any record is created that records a person's name that what is actually recorded is a numeric value from the Personnel_PK field of LUtblPersonnel.
LUtblPersonnel also has a field called "CanOverride" which acts in similar fashion as the field called "Role" but - in my thinking - is a much more direct route to defining who can and who cannot make the decision to allow questionable product to continue being rolled - especially because that person could be QC, Admin, or Supervisor. And I definitely want to differentiate between the three.
I want to be able to run a report that gives me all of the records and details of an occurrence of a production line be stopped. This query would need to include every table you see in the screen shot except for tblAssemblyComponents which is also a junction table between tblParts and tblFinalProducts.
If I create a query on just tblInspectionEvent I know how to return the actual names of Inspector and Operator but when I include the field "StopOverrideBy" in tblLineStop all I know how to do is return the numeric value of Personnel_PK that was recorded from a query-based combo box on frmLineStop.
So it seems to me that I need a "query-within-a-query" to return the name of the person who made the decision to override QC. I thought this meant a subquery.
As always, I am grateful for any insight. I would attach a copy of the db but you are only seeing a small portion of it in this screenshot. To try to "edit" it to share just the relevant bits - at this point - would be an enormous project all by itself.
Again - Thank You for any insight,
Tim