How write SQL with a double reference the same table

[Western_Neil]

Hello;
I'm trying to get a VBA SQL statement to work. The schema I have is:
Table 1 tblElmts ElmtID ElmtTag ElmtTitle ElmtNote
Table 2 tblSubElmts SubElmtID UpperElmtFK ElmtQty LowerElmtFK SubElmtNote
Relationships are 1 to many between the ID and the FKs (both)

This SQL works:
SELECT tblElmts.ElmtID, tblSubElmts.SubElmtID, tblSubElmts.UpperElmtFK, tblElmts.ElmtTag," _


& " tblSubElmts.ElmtQty, tblSubElmts.LowerElmtFK, tblSubElmts.SubElmtNote" _
& " FROM tblElmts INNER JOIN tblSubElmts ON tblElmts.ElmtID = tblSubElmts.UpperElmtFK" _
& " WHERE UpperElmtFK=37"
But only gives the Upper or Lower ElmtTag. depending on UpperElmtFK or LowerElmtFK use in the ON clause.
I was trying to place an “AS tbl2up” in the JOIN, which fails, to allow placing the second one (Lower) in as a subquery. The question is how to return both ElmtTag s in the query. I do realise that ElmtID will equal UpperElmtFK , giving the same Upper ElmtTag. I would prefer not to remove this at this time.

A 2nd question would be "Is it better make the bottom subquery the most complex and then work out, or just cut the size down the quickest?

Thanks for Looking

[June7]

Use Access query designer. Pull tblElmts into table/query window twice and link each to tbl SubElmts. Then switch to SQLView to see the statement.

[isladogs]

Qu2: cut the size down the quickest so the other subquery has a smaller dataset to handle

[Western_Neil]

Thanks June, but I get a Null return. Rereading it I guessing I'm not doing the linking right. How should the designer look with the right link?

[Western_Neil]

Thanks Colin. That I thought, smallest and then muck with it

[June7]

See if this helps https://element121.com/2015/11/07/ho...ery-statement/