Results 1 to 1 of 1
  1. #1
    sap_spectre is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021

    MS Access select query inside another select query for selecting another value from the same table

    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]:
    Click image for larger version. 

Name:	tblBom view.PNG 
Views:	17 
Size:	109.9 KB 
ID:	45513

    My Desired output [excel view]:
    Click image for larger version. 

Name:	desired output.PNG 
Views:	18 
Size:	15.1 KB 
ID:	45514

    My Query:

    SELECT tblBom.processname AS Process,
    tblBom.child AS [SAP Code],
    tblBom.childname AS Material,
    (SELECT childqty FROM tblBom WHERE tblBom.child like (SELECT father FROM tblBom WHERE child Like tblBom.father)),
    ) 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 ??

    Last edited by sap_spectre; 06-16-2021 at 02:18 AM. Reason: My query added

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-10-2019, 11:10 PM
  2. Replies: 9
    Last Post: 02-07-2018, 08:01 AM
  3. Replies: 3
    Last Post: 06-01-2017, 06:57 AM
  4. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  5. Replies: 5
    Last Post: 08-02-2012, 09:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums