Subquery help (1 Viewer)

[jdraw]

Tim,

I just used a table that I had. It has a field pName that has both First and Last Name in that field. I my example there was no concatenation as such -- for example a couple of pName values were
Polly Dactyl and Ura Payne. I would not normally put both First Name and Last Name in one field.

If the PersonNames table had FirstName and LastName as separate fields, then you would select both and you will get the appropriate values for InspectorsName.FirstName, InspectorsName.LastName, as well as PersonsNames.FirstName, PersonsNames.LastName etc.

Bottom line there was no concatenation.
Sorry for any confusion.

 

[Zydeceltico]

Tim,

I just used a table that I had. It has a field pName that has both First and Last Name in that field. I my example there was no concatenation as such -- for example a couple of pName values were
Polly Dactyl and Ura Payne. I would not normally put both First Name and Last Name in one field.

If the PersonNames table had FirstName and LastName as separate fields, then you would select both and you will get the appropriate values for InspectorsName.FirstName, InspectorsName.LastName, as well as PersonsNames.FirstName, PersonsNames.LastName etc.

Bottom line there was no concatenation.
Sorry for any confusion.

Thanks JDraw - - Because I'm a bit thick headed -and a little bit distracted by current conditions and how they're affecting daily plans at the shop, would you mind showing me some SQL that would concatenate the name fields?

I apologize if seem "lazy" - just very grateful for your time and a little overwhelmed at the moment.

Thanks,

Tim

 

[jdraw]

Sure.
I added field pFirstName and pLastName to table PersonNames and valued the fields.
created a select query to get the data and to concatenate the pFirstName and pLastname into a single field. The pFirstName and pLastname are separated by " " a space for readability.

Here's the revised query and output.

SELECT tblDemoRoles.stoppageId
    ,tblDemoRoles.stopaggestart
    ,tblDemoRoles.stoppageend
    ,tblDemoRoles.stoppedBy
    ,tblDemoRoles.InspectedBy
    ,tblDemoRoles.coffeeServedby
    ,PersonNames.pFirstName & " " & PersonNames.pLastName AS concatPerson
    ,Inspectors.pFirstName & " " & Inspectors.pLastName AS ConcatInspector
    ,CoffeeServers.pFirstName & " " & CoffeeServers.pLastName AS ConcatCoffeeServer
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;

qDemoForTimUsingRoles

stoppageId	stopaggestart	stoppageend	stoppedBy	InspectedBy	coffeeServedby	concatPerson	ConcatInspector	ConcatCoffeeServer
2​	12-Mar-20 8:15:00 AM​	12-Mar-20 9:47:00 AM​	4​	6​	8​	Bern Nelroy	Claude Van Dam	Ura Payne
3​	12-Mar-20 7:35:00 PM​	12-Mar-20 11:16:00 PM​	2​	3​	7​	Felix Genth	A.F. Berth	Polly Dactyl

 

[Zydeceltico]

Sure.
I added field pFirstName and pLastName to table PersonNames and valued the fields.
created a select query to get the data and to concatenate the pFirstName and pLastname into a single field. The pFirstName and pLastname are separated by " " a space for readability.

Here's the revised query and output.

SELECT tblDemoRoles.stoppageId
    ,tblDemoRoles.stopaggestart
    ,tblDemoRoles.stoppageend
    ,tblDemoRoles.stoppedBy
    ,tblDemoRoles.InspectedBy
    ,tblDemoRoles.coffeeServedby
    ,PersonNames.pFirstName & " " & PersonNames.pLastName AS concatPerson
    ,Inspectors.pFirstName & " " & Inspectors.pLastName AS ConcatInspector
    ,CoffeeServers.pFirstName & " " & CoffeeServers.pLastName AS ConcatCoffeeServer
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;

qDemoForTimUsingRoles

stoppageId	stopaggestart	stoppageend	stoppedBy	InspectedBy	coffeeServedby	concatPerson	ConcatInspector	ConcatCoffeeServer
2​	12-Mar-20 8:15:00 AM​	12-Mar-20 9:47:00 AM​	4​	6​	8​	Bern Nelroy	Claude Van Dam	Ura Payne
3​	12-Mar-20 7:35:00 PM​	12-Mar-20 11:16:00 PM​	2​	3​	7​	Felix Genth	A.F. Berth	Polly Dactyl

View attachment 79908 View attachment 79908

JDraw - THANK YOU

 

[jdraw]

Tim,
You are welcome. Keep on truckin'.

 

[Zydeceltico]

Tim,
You are welcome. Keep on truckin'.

Hi JDraw,

Thought you might enjoy seeing the SQL that I finally got to work with your assistance - then I have a question

Here's the code:

SELECT tblinspectionevent.datetime,
       [jobnumber] & [task] & [resource]                        AS Job,
       OverRodeBy.firstname & "" & OverRodeBy.lastname          AS OverRideBy,
       [Inspectors].[firstname] & "" & [Inspectors].[lastname] AS InspectedBy,
       [Operators].[firstname] & "" & [Operators].[lastname]   AS Operator
FROM   lutblpersonnel AS Operators
       INNER JOIN (lutblpersonnel AS Inspectors
                   INNER JOIN (lutblpersonnel AS OverRodeBy
                               INNER JOIN ((tbljobs
                                            INNER JOIN tblinspectionevent
                                                    ON tbljobs.[job_id] = tblinspectionevent.[job_fk])
                            INNER JOIN tbllinestop
                                ON tblinspectionevent.[inspectionevent_pk] = tbllinestop.[inspectionevent_fk])
ON OverRodeBy.personnel_pk = tbllinestop.stopoverrideby)
ON Inspectors.personnel_pk = tblinspectionevent.inspector_fk)
ON Operators.personnel_pk = tblinspectionevent.operator_fk;

Like I said - works like a charm.....and I can repeat the process for other queries.
My question is singularly for my own understanding of the logic of the code above - specifically the FROM clause.

I should add that I made alias tables named: Inspectors, Operators, and OverRodeBy.

OverRodeBy is related to tblLineStop. Inspectors and Operators are related to tblInspectionEvent.

I don't understand why all of the INNER JOINS are nested - and I especially don't understand the seeming hierarchy of INNER JOINS. Studying it a bit more, are all of the INNER JOINS in reverse hierarchical order? Why are the ...AS Operators, ....AS Inspectors, ........AS OverRodeBy parenthetical to each other? I don't understand the logic - trying - but not getting there.

Thanks!

Tim

 

[jdraw]

Good stuff. Glad it is working for you.

I think the nesting and parentheses of the inner joins is for the efficient parsing of the statement by the SQL processor. As for the indentation, my view is that it for human consumption/user ease of understanding/readability. I have not been able to find a definitive statement as to the structure of the MS Access SQL for multi table joins. Not all DBMSs use the parentheses.

Perhaps someone else has a more definitive or even mathematical rationale for the MS Access SQL

 

[The_Doc_Man]

As to the parentheses, if you use the query builder you will see Lots of Insipid Silly Parentheses - almost as bad as the LISP language. But seriously, you get more than that from query builder. I think the parentheses are there because QB steps through the grid one element at a time until it has everything it needs, and whenever it adds a new table, I guess it needs to encapsulate what it had before. We normally don't think of the QB internals, but as we ALL know, this stuff doesn't spring from the brow of Zeus already intact and full-grown. It is built by a stepwise program just like the stuff we write ourselves.

 

[Minty]

I think the Access gnomes are on a bonus for each set of parentheses they can stick into a select statement.

 

[The_Doc_Man]

Obviously subsidized by the makers of parentheses.

 

[Zydeceltico]

Obviously subsidized by the makers of parentheses.

Like virtually every thing else..HA