Results 1 to 3 of 3
  1. #1
    udigold1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2010
    Posts
    29

    Passing a value to subquery?

    Hi,


    I'm having a problem with a query I've built. The query's purpose is to give a balance sheet of provident funds securities' holdings.
    all works well but I needed to add the percentage of each security in relation of it's fund.
    So I had to build a subquery that gives that divide the security to the total value of the fund (the bold item in the select statement).
    But it doesn't work because it returns a result for each and every fund so the query fails (at most one record can be returned...).
    I've tried to pass tb3_Accounts.tb3_LeadingFundCode from the main query as an alias to the subquery but that doesn't work, as well.

    Anyone has an idea what am I doing wrong?

    Thanks,

    Code:
    SELECT
    tb5_Fund.tb5_FundName,
    tb3_Accounts.tb3_LeadingFundCode as FundID,
    DataIn.tb1_securitynum,
    DataIn.tb1_descSecurity,
    DataIn.tb1_Balance,
    DataIn.tb1_FaceValue,
    DataIn.tb1_ISIN AS ISIN,
     tb20_InvestTool.tb20_InvestToolNum,
    tb10_Afik.tb10_AfikIdEntity,
    DataIn.tb1_Balance / (SELECT Sum(DataIn.tb1_Balance) AS SumOftb1_Balance
    FROM tb5_Fund INNER JOIN (tb3_Accounts INNER JOIN DataIn ON tb3_Accounts.tb3_AccountNumber = DataIn.tb1_accountNumber) ON tb5_Fund.tb5_FundCode = tb3_Accounts.tb3_LeadingFundCode
    GROUP BY tb5_Fund.tb5_FundCode, tb5_Fund.tb5_FundName, tb3_Accounts.tb3_LeadingFundCode
    HAVING (((tb5_Fund.tb5_FundCode)= tb3_Accounts.tb3_LeadingFundCode))))
     
    FROM
    tb5_Fund INNER JOIN (tb20_InvestTool INNER JOIN
    (tb16_currency INNER JOIN (tb11_AfikDerivative RIGHT JOIN (tb33_IRSSecurityot RIGHT JOIN (tb12_Nose INNER JOIN (tb9_ErpEntity INNER JOIN (tb32_DerivativeSec RIGHT JOIN (tb31_ForwardSecurityot RIGHT JOIN (tb17_CurrencyLink INNER JOIN (tb10_Afik INNER JOIN (tb15_Securities RIGHT JOIN (Megama RIGHT JOIN (tb3_Accounts INNER JOIN DataIn ON tb3_Accounts.tb3_PortfolioNumber = DataIn.tb1_codeFund) ON Megama.tb2_Megama_code = DataIn.tb1_securitynum) ON tb15_Securities.tb15_SecurityID = DataIn.tb1_securitynum) ON (tb10_Afik.tb10_AfikID = DataIn.tb1_afik) AND (tb10_Afik.tb10_Erp = DataIn.tb1_ERP)) ON (tb17_CurrencyLink.tb17_ErpCode = DataIn.tb1_ERP) AND (tb17_CurrencyLink.tb17_ErpCurrencyCode = DataIn.tb1_CurrencyCode)) ON tb31_ForwardSecurityot.tb31_SecurityNum = tb15_Securities.tb15_SecurityID) ON tb32_DerivativeSec.tb32_SecurityNum = tb15_Securities.tb15_SecurityID) ON (tb9_ErpEntity.tb9_ErpID = DataIn.tb1_ERP) AND (tb9_ErpEntity.tb9_EntityID = DataIn.tb1_Entity)) ON tb12_Nose.tb12_noseID = tb10_Afik.tb10_NoseID) ON tb33_IRSSecurityot.tb33_SecurityId = tb15_Securities.tb15_SecurityID) ON tb11_AfikDerivative.tb11_DerivativeID = tb32_DerivativeSec.tb32_DerivativeID) ON tb16_currency.tb16_CurrencyID = tb17_CurrencyLink.tb17_CurrencyID) ON tb20_InvestTool.tb20_InvestToolNum = tb10_Afik.tb10_InvestTool) ON tb5_Fund.tb5_FundCode = tb3_Accounts.tb3_LeadingFundCode
     
    WHERE (((tb15_Securities.tb15_IsActiveRegister)=Yes) AND ((DataIn.tb1_Entity)=1))

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This would probably be much easier to do in report design using Sorting & Grouping features with aggregate calcs in textboxes. But don't know your db so only guessing.

    Otherwise, build a query that aggregates the fund then in another query JOIN to other dataset and do percentage calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    udigold1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2010
    Posts
    29
    Thanks! I did the joined query like you've suggested and it works like magic :-)

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

Similar Threads

  1. A subquery inside a subquery
    By tbjmobile@gmail.com in forum Queries
    Replies: 15
    Last Post: 04-22-2018, 11:48 AM
  2. Replies: 17
    Last Post: 02-25-2018, 02:37 AM
  3. Subquery Help
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 12-05-2017, 01:57 PM
  4. Subquery
    By Daryl2106 in forum Access
    Replies: 20
    Last Post: 01-13-2013, 07:21 AM
  5. Subquery
    By combine21 in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 04:33 PM

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