Subquery help (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Hi All -

I've attached a little test db.

I'm trying to wrap my head around subqueries to help with creating reports.

I have a table named tblDecisions that would typically have a form to record instances of decisions being made and who made them. The thing is I have a single table for all people - some can make decisions and others cannot :) tblDecisions has a field called DecisionMadeBy that references Personnel_ID in tblPersonnel.

Table tblPersonnel is a lookup table of all the people with a field called DecisionMaker with 1=can make a decision and 0= no, cannot make a decision.

How do I write a query based on tblDecisions that returns the First and Last name of the appropriate person in tblPersonnel?

Thanks a bunch in advance,

Tim
 

Attachments

  • Subquery Question.zip
    17.8 KB · Views: 87

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:41
Joined
May 21, 2018
Messages
8,463
I am not sure what you are trying to do, but that table design does not make any sense to me. I think you at a minimum would want a many to many table Where multiple people can make multiple decisions on multiple issues (projects, etc) Maybe table
tblPersons_Decisions
issueID_FK
personID_KF
DecisionID_FK

This way a record 1,1,2 is person 1 deciding no on issue

Can you explain what you are trying to do table wise.
 

HiTechCoach

Well-known member
Local time
Today, 00:41
Joined
Mar 6, 2006
Messages
4,357
I have a table named tblDecisions that would typically have a form to record instances of decisions being made and who made them. The thing is I have a single table for all people - some can make decisions and others cannot :) tblDecisions has a field called DecisionMadeBy that references Personnel_ID in tblPersonnel.

Tim,

Not sure you need a subquery.

That sounds link a simple join between tblDecisions.DecisionMadeBy and tblPersonnel.Personnel_ID where tblPersonnelDecisionMaker w = 1
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Jan 23, 2006
Messages
15,364
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.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    135.8 KB · Views: 81

jdraw

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Jan 23, 2006
Messages
15,364
Tim,

Have you worked with a picture of your model(pencil and paper or a mockup) and some test scenarios to prove the "what you need"?
Sometimes working with some test conditions eg- line is stopped for reason A or B.., then Q happens; here is what we expect to happen in the business.... you will find this additional analysis can identify new things, redundant things or even a more streamlined approach(es). This is what I refer to as "stump the model". Once you have determined WHAT you need to record, then show us that and any attempts to design the query to do that recording (aka this is the HOW). I'm sure readers will offer more focused advice as the options are evaluated.
Good luck.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Tim,

Have you worked with a picture of your model(pencil and paper or a mockup) and some test scenarios to prove the "what you need"?
Sometimes working with some test conditions eg- line is stopped for reason A or B.., then Q happens; here is what we expect to happen in the business.... you will find this additional analysis can identify new things, redundant things or even a more streamlined approach(es). This is what I refer to as "stump the model". Once you have determined WHAT you need to record, then show us that and any attempts to design the query to do that recording (aka this is the HOW). I'm sure readers will offer more focused advice as the options are evaluated.
Good luck.
I have worked this out - with pencil and paper - many times.

Model is definitely that "line is stopped for reason A or B.., then Q happens."

Yesterday provides the perfect example of the two - and only two - possible scenarios. I stopped a production line twice for two different reasons. Once in the morning - Job1. Once in the afternoon - Job2.

In the morning (Instance1), I stopped the line for ten minutes because the rollers had gotten dirty and were removing the coating from the steel as the material moved through the mill. I recorded a start time, inspector's name who stopped the mill (in this case me), mill number, operator, job number, reason for stopping the line. After the mill was cleaned and we were running again, I record that restart time.

In the afternoon, the same mill had moved on to a different job with a different coil. I stopped the mill again when we noticed questionable material coming off the coil and into the sixty foot long mill. We discussed our options for about 15 minutes. I had the operator run two more 25 foot long pieces through the mill to see how the product would be affected by the questionable material. The effect of rolling the questionable material through the mill greatly minimized what could have been a messy product. The measurable condition was minutely outside of tolerance - but only minutely and this was our last coil of the correct size. I decided to "Stop The Override" and let the mill finish its production run for a variety of reasons.

Thus, in this instance my Personnel_PK is recorded in both "Inspector_FK" in tblInspectionEvent and also in "StopOverrideBy" in tblLineStop.

Is that any clearer?

Thanks!

Tim
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:41
Joined
Feb 28, 2001
Messages
27,001
What you describe is not a sub-query case. What you really want is a JOIN case where you have your stoppage incident joining to the table of people on having the person ID in both tables. Then you can look up the details from the person because Access will "virtually" join the two tables together (while the query is open). The name of the person is thus in the same virtual record as the stoppage. This will be a classic many-to-one JOIN case since many stoppages can be attributed to one person.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Tim,

Once you have determined WHAT you need to record, then show us that and any attempts to design the query to do that recording (aka this is the HOW). I'm sure readers will offer more focused advice as the options are evaluated.
Good luck.
Hi again JDraw. Here is the SQL I've been trying to make work.

Code:
SELECT tblinspectionevent.datetime,
       [jobnumber] & [task] & [resource] AS JobName,
       tblfinalproducts.finalproducttype,
       tblinspectionevent.inspector_fk,
       tblinspectionevent.operator_fk,
       tbllinestop.linestopbegin,
       tbllinestop.linestopend,
       lutbllinestopreasons.linestopreason,
       tbllinestop.stopoverrideby
FROM   (((tblfinalproducts
          INNER JOIN tbljobs
                  ON tblfinalproducts.finalproduct_id = tbljobs.finalproduct_fk)
         INNER JOIN tblinspectionevent
                 ON tbljobs.job_id = tblinspectionevent.job_fk)
        INNER JOIN tblinspectionnotes
                ON tblinspectionevent.inspectionevent_pk =
                   tblinspectionnotes.inspectionevent_fk)
       INNER JOIN (lutbllinestopreasons
                   INNER JOIN tbllinestop
                           ON lutbllinestopreasons.linestopreason_id =
                              tbllinestop.linestopreason)
               ON tblinspectionevent.inspectionevent_pk =
                  tbllinestop.inspectionevent_fk;

Attached is a screenshot of the resulting datasheet. Notice the fields Inspector_FK, Operator_FK, and StopOverrideBy. How can I have actual names returned instead of the Personnel_ID?
 

Attachments

  • Capture.JPG
    Capture.JPG
    55.5 KB · Views: 76

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
What you describe is not a sub-query case. What you really want is a JOIN case where you have your stoppage incident joining to the table of people on having the person ID in both tables. Then you can look up the details from the person because Access will "virtually" join the two tables together (while the query is open). The name of the person is thus in the same virtual record as the stoppage. This will be a classic many-to-one JOIN case since many stoppages can be attributed to one person.
Hi Doc. I am following you - but I am also quite inexperienced with creating SQL JOINS. Please bear with me.

I believe you are saying that I create the join in SQL and it is not the case that I need to literally define a relationship between the Personnel table and the line stop table. I'm pretty sure that's what you are telling me.

If that is the case, where in my existing SQL do I insert another JOIN? I am assuming this would be another INNER JOIN.

AND.....how do I concatenate the names? Again - I am assuming this must done in SQL View and actually written - in which case I would assume that it would look much the same as the expression I used to concatenate the Job Number.

Am I understanding correctly?

Thank you,

Tim
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Jan 23, 2006
Messages
15,364
Here is a snippet I responded to a similar (concept) a while back.
This concept may also be called Reference Tables, or a role/rolename in modelling.
eg. ISOCountryCodes

each of these could get their appropriate values from the ISOCountry table.
SupplierCountry
SellerCountry
ShipperCountry

You can use your lutblPersonnel multiple times in your query - each reflecting/representing the role played by that person.

Are you sure you need all those tables in your query? I ask because I see
tblinspectionnotes but I don't see that it is used in your query results. As always, you know the details much better than readers, so I could easily overlook the obvious.

Also you said
I recorded a start time, inspector's name who stopped the mill (in this case me), mill number, operator, job number, reason for stopping the line. After the mill was cleaned and we were running again, I record that restart time
How did you do that --via form?
Continued good luck.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Here is a snippet I responded to a similar (concept) a while back.


You can use your lutblPersonnel multiple times in your query - each reflecting/representing the role played by that person.

Are you sure you need all those tables in your query? I ask because I see
tblinspectionnotes but I don't see that it is used in your query results. As always, you know the details much better than readers, so I could easily overlook the obvious.
Continued good luck.
Thanks jdraw! Yeah - I do need tblInspectionNotes as well - I just left it out for the moment because I felt it would just add to screen clutter and isn't really an issue. That's the only reason I left it out. That part I get. :)

I believe between your advice and Doc's I have enough to make another stab at the SQL.

I'll give it a go and let you know how it works out.

Thanks,

Tim
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Jan 23, 2006
Messages
15,364
Good stuff.
Here's a graphic showing the use of PersonNames table multiple times.
DemoUsingPersonTableForMultipleRoles.PNG
DemoUsingPersonTableForMultipleRoles.PNG

And some SQL to get the names and Ids
Code:
SELECT tblDemoRoles.stoppageId
    ,tblDemoRoles.stopaggestart
    ,tblDemoRoles.stoppageend
    ,tblDemoRoles.stoppedBy
    ,tblDemoRoles.InspectedBy
    ,tblDemoRoles.coffeeServedby
    ,PersonNames.pName
    ,Inspectors.pName
    ,CoffeeServers.pName
FROM PersonNames AS CoffeeServers
INNER JOIN (
    PersonNames AS Inspectors INNER JOIN (
        PersonNames INNER JOIN tblDemoRoles
            ON PersonNames.id = tblDemoRoles.stoppedBy
        )
        ON Inspectors.id = tblDemoRoles.InspectedBy
    )
    ON CoffeeServers.id = tblDemoRoles.coffeeServedby;

A few dummy records to show result --This is just a mock up . DemoUsingPersonTableForMultipleRoles.PNG

Query59

stoppageIdstopaggestartstoppageendstoppedByInspectedBycoffeeServedbyPersonNames.pNameInspectors.pNameCoffeeServers.pName
2​
12-Mar-20 8:15:00 AM​
12-Mar-20 9:47:00 AM​
4​
6​
8​
Bern NelroyClaude Van DamUra Payne
3​
12-Mar-20 7:35:00 PM​
12-Mar-20 11:16:00 PM​
2​
3​
7​
Felix GenthA.F. BerthPolly Dactyl
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Good stuff.
Here's a graphic showing the use of PersonNames table multiple times.
View attachment 79795 View attachment 79795

And some SQL to get the names and Ids
Code:
SELECT tblDemoRoles.stoppageId
    ,tblDemoRoles.stopaggestart
    ,tblDemoRoles.stoppageend
    ,tblDemoRoles.stoppedBy
    ,tblDemoRoles.InspectedBy
    ,tblDemoRoles.coffeeServedby
    ,PersonNames.pName
    ,Inspectors.pName
    ,CoffeeServers.pName
FROM PersonNames AS CoffeeServers
INNER JOIN (
    PersonNames AS Inspectors INNER JOIN (
        PersonNames INNER JOIN tblDemoRoles
            ON PersonNames.id = tblDemoRoles.stoppedBy
        )
        ON Inspectors.id = tblDemoRoles.InspectedBy
    )
    ON CoffeeServers.id = tblDemoRoles.coffeeServedby;

A few dummy records to show result --This is just a mock up . View attachment 79795

Query59

stoppageIdstopaggestartstoppageendstoppedByInspectedBycoffeeServedbyPersonNames.pNameInspectors.pNameCoffeeServers.pName
2​
12-Mar-20 8:15:00 AM​
12-Mar-20 9:47:00 AM​
4​
6​
8​
Bern NelroyClaude Van DamUra Payne
3​
12-Mar-20 7:35:00 PM​
12-Mar-20 11:16:00 PM​
2​
3​
7​
Felix GenthA.F. BerthPolly Dactyl
That's a great mockup. Thanks!. The only thing that is different from mine is that you have separate tables for coffee servers and inspectors whereas I currently have "coffee servers" and "inspectors" all in the same table. If I'm following correctly that shouldn't matter though - should it?
 

Minty

AWF VIP
Local time
Today, 05:41
Joined
Jul 26, 2013
Messages
10,355
That's a great mockup. Thanks!. The only thing that is different from mine is that you have separate tables for coffee servers and inspectors whereas I currently have "coffee servers" and "inspectors" all in the same table. If I'm following correctly that shouldn't matter though - should it?
I'll jump in as JDraw is offline atm.
No they are all the same table ;

FROM PersonNames AS CoffeeServers
INNER JOIN (
PersonNames AS Inspectors

This is called aliasing a table name, you could have called them PersonName1 & PersonName 2.

Simply drag the same table into an access query 3 time and see what happens.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Jan 23, 2006
Messages
15,364
Thanks Minty. I've been off line. Great answer. 3 copies of the same table. Access will default to Tablename_1, Tablename_2, Tablename_3 but you can alias them to whatever you like.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Thanks very much folks! I've been off "doing my day job" - ha - so I'll give it a go now.
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
I'll jump in as JDraw is offline atm.
No they are all the same table ;

FROM PersonNames AS CoffeeServers
INNER JOIN (
PersonNames AS Inspectors

This is called aliasing a table name, you could have called them PersonName1 & PersonName 2.

Simply drag the same table into an access query 3 time and see what happens.
Thank You Minty!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:41
Joined
Feb 28, 2001
Messages
27,001
Zydeceltico - I was away for a while due to multiple shopping runs, due in turn to side effects of the corona pandemic. In my absence, I see you got other help.

Technically, you DON'T have to form a permanent relationship between the Personnel and LineStop tables. But in the query designer screen, if you have both tables in the graphics area above the grid, you can assert a relationship between the two person IDs - from the personnel table and the line stop table. Access can work that way. You DO have to have some kind of relationship for the Access query designers to work with you on the JOIN.

You got some good help from the others, but you did ask about the type of JOIN. It could be an INNER join but there is no reason it couldn't be one of the OUTER JOIN "flavors." If you have that relationship set up correctly, Access can figure it out for you. I'll just point out that you probably can have a relationship that says "Show me all entries from the LineStop table and only matching entries from the Personnel table."
 

Zydeceltico

Registered User.
Local time
Today, 01:41
Joined
Dec 5, 2017
Messages
843
Good stuff.
Here's a graphic showing the use of PersonNames table multiple times.
View attachment 79795 View attachment 79795

And some SQL to get the names and Ids
Code:
SELECT tblDemoRoles.stoppageId
    ,tblDemoRoles.stopaggestart
    ,tblDemoRoles.stoppageend
    ,tblDemoRoles.stoppedBy
    ,tblDemoRoles.InspectedBy
    ,tblDemoRoles.coffeeServedby
    ,PersonNames.pName
    ,Inspectors.pName
    ,CoffeeServers.pName
FROM PersonNames AS CoffeeServers
INNER JOIN (
    PersonNames AS Inspectors INNER JOIN (
        PersonNames INNER JOIN tblDemoRoles
            ON PersonNames.id = tblDemoRoles.stoppedBy
        )
        ON Inspectors.id = tblDemoRoles.InspectedBy
    )
    ON CoffeeServers.id = tblDemoRoles.coffeeServedby;

A few dummy records to show result --This is just a mock up . View attachment 79795

Query59

stoppageIdstopaggestartstoppageendstoppedByInspectedBycoffeeServedbyPersonNames.pNameInspectors.pNameCoffeeServers.pName
2​
12-Mar-20 8:15:00 AM​
12-Mar-20 9:47:00 AM​
4​
6​
8​
Bern NelroyClaude Van DamUra Payne
3​
12-Mar-20 7:35:00 PM​
12-Mar-20 11:16:00 PM​
2​
3​
7​
Felix GenthA.F. BerthPolly Dactyl

Hi JDraw,

I think I've finally deciphered the logic of the above SQL example you most generously took the time to show me.

I do have a question. In your SELECT clause you have "PersonsName.pName, InspectorsName.pname,...."etc. Then in the resultant datasheet you show first and last names concatenated in a single field but your graphic of the model of table aliases only has a single field name for "pName." I have a field for both FirstName and LastName. How would I modify the SQL to concatenate my name fields?

Or................am I missing something? :)

Thanks,

Tim
 

Users who are viewing this thread

Top Bottom