MS Access select query inside another select query for selecting another field value

Question

I don't think of any better way.

What I am trying to do is grab some data from table where the field childitem="NL" and the complex part in selecting fields to show is that, I need to make new field Consumption but this field value will be same childqty but when the field processname first 2 character is SC, I need to make the Consumption value to a childqty of another field: current father (4-SCF-329...[id:30]) is child to another father (4-FCM-3290...[id:17]) which is indeed child to another father (4-MCS-329....[id:21])

My Query:

SELECT tblBom.processname AS Process,
    tblBom.child AS [SAP Code],
    tblBom.childname AS Material,
    iif(LEFT(tblBom.processname,2)="SC", 
        (SELECT childqty FROM tblBom WHERE tblBom.child like (SELECT father FROM tblBom WHERE child Like tblBom.father)), 
        tblBom.childqty
    ) AS consumption,
    tblBom.childrate AS [Landed Rate],
    tblBom.childrate * tblBom.childqty As RATE
FROM tblBom
WHERE tblBom.childitem Like "NL";

I want the tblBom.father last in the 5th line of code to be the main Queries father. Is there a way to make it as variable somewhere or any way. If I change it to "4-SCF*" that will work.

My Table

Excel Detailed View:

Orange: Condition checking for word "SC"
Yellow: The value that need to be replaced
Green: The value that will be placed on the yellow
ReddishPink: The path to Green value for query

Desired OutPut

Maybe: `LIKE [enter some value] & "*"`. Or reference a form control for variable input. — June7, Jun 15 '21 at 17:15
The thing is, I can't predict what will be in field `father`. All I want is get the `father` of main SELECT query and pass it to sub sub SELECT query. — user9811098, Jun 16 '21 at 04:35
still too confusing for me. Please show desired query output and all important parts of source table. In particular we need to see the full values of father. — mazoula, Jun 16 '21 at 04:37
Can I share you the table as an xlsx file, I am getting this table from a another complicated big table. So the values in tblBom will change when I run the main query. That is why I need to lie on the complex 2 sub queries inside Main query here — user9811098, Jun 16 '21 at 04:41
https://gofile.io/d/x4JXoH : First sheet the tblBom, second sheet is what is the output am looking for. I have highlighted some cells with color, where I am using query. — user9811098, Jun 16 '21 at 04:52
Cannot attach files in SO and many will not download from external source. Might want to post in a forum that does allow attaching files. — June7, Jun 16 '21 at 05:08
I have included the table image in excel view for more clarity. — user9811098, Jun 16 '21 at 10:53

score 0 · Answer 1 · answered Jun 17 '21 at 08:33

My solution requires two calculated fields and two queries. The calculated fields are UltimateFather and consumption. we require two queries because the problem doesn't guarantee that a child's father has a value of childitem like "NL". Hence we can't throw out any data before calculating each child's UltimateFather. Ultimate Father is based on adding the public function GetUltimateFather to a code module. Getting the UltimateFather is a recursive problem so see here for commentary on recursive functions in queries: Is it possible to create a recursive query in Access?

'calculated fields
UltimateFather: GetUltimateFather([child])
consumption: IIf([processname] Like ("SC*"),DLookUp("childqty","tblBom","father = '" & [UltimateFather] & "'"),[childqty])

Public Function GetUltimateFather(childid As String) As String
Dim currentchild, hasfather As String
currentchild = childid
'string variables make sure to escape and delimit strings properly'
hasfather = Nz(DLookup("father", "tblBom", "child = '" & currentchild & "'"), "")
If hasfather = "" Then
GetUltimateFather = currentchild
Else
GetUltimateFather = GetUltimateFather(hasfather) 'get next father in chain
End If
End Function

MS Access select query inside another select query for selecting another field value

1 Answers1