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 in same table:= current father (4-SCF-329...[row:31]) is child to another father (4-FCM-3290...[row:18]) which is indeed child to another father (4-MCS-329....[row:22]).
The tblBom is like [excel view]:
My Desired output [excel view]:
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 to somehow pass father in the Main SELECT query to the highlighted tblBom.father. That way it works perfectly (I have tested it by manually entering that father value there "4-SCF-3290-BK-G").
The table tblBom is deleted and created with new values when I run a query, so I can't enter a fixed value there, I need exactly the father of main query where condition satisfy. Any options ??