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