locked
Need assistance with addition JOIN in FROM clause RRS feed

Answers

  • Hi,

    Is this related to your other question?

    https://social.msdn.microsoft.com/Forums/office/en-US/58ad160b-0c98-4c5d-85b1-f820a26b3ad2/trouble-adding-another-join-can-anyone-see-why-there-is-an-issue?forum=accessdev

    Not sure if double-posting is allowed here.

    Good luck!

    Tuesday, September 25, 2018 3:50 PM
  • FROM tblActuals 
    LEFT JOIN [CC Tab] ON tblActuals.CostCenter = [CC Tab].[Cost Center]
    LEFT JOIN [COA Tab] ON tblActuals.CostElement = [COA Tab].[COA]
    LEFT JOIN [IO Tab] ON tblActuals.IO = [IO Tab].[IO Shortcode] 
    LEFT JOIN [LT-Project_Portfolio] ON [IO Tab].[PFP Alias] = [LT-Project_Portfolio].[Alias];

    By the way, it is not good practice to have some spaces in the table names.


    A Fan of SSIS, SSRS and SSAS


    Tuesday, September 25, 2018 4:42 PM
  • Thank you, sir!

    However, removing the parens gave me a "missing operator" error...

    Can you show your exact code after removing parens?

    It should look like below as per your posted query

    SELECT 
    	 [CC Tab].Division
    	, [CC Tab].Function
    	, [CC Tab].[Sub-Function]
    	, [COA Tab].[Account Summary]
    	, [COA Tab].Mapping
    	, [COA Tab].[TM1 Cost Package Name]
    	, [IO Tab].[PFC Alias]
    	, [IO Tab].[PFP Alias]
    	, [IO Tab].IO
    	, [IO Tab].[IO Shortcode]
    	, tblActuals.Scenario
    	, tblActuals.Monthly_Variance_Amount
    	, tblActuals.YTD_Actuals
    	, tblActuals.YTD_Plan
    	, tblActuals.YTD_Var_to_Plan
    	, tblActuals.Plan
    	, tblActuals.Keeper
    	, [CC Tab].[Report Grouping]
    	, tblActuals.RefDocumentNumber
    	, tblActuals.[Aux#acctassignment_1]
    	, tblActuals.CostCenter
    	, tblActuals.CostElement
    	, tblActuals.Cost_element_name
    	, tblActuals.[Val/COAreaCrcy]
    	, tblActuals.Period
    	, tblActuals.Nameofoffsettingaccount
    	, tblActuals.DocumentHeaderText
    	, tblActuals.Purchasing_Document
    	, tblActuals.Purchase_order_text
    	, tblActuals.FiscalYear
    	, tblActuals.UserName
    	, tblActuals.[Ref#companycode]
    	, tblActuals.Name
    	, [COA Tab].[Cost Package]
    	, [COA Tab].[Cost Package Detail]
    	, [COA Tab].[Sub-Cost Package]
    	, [COA Tab].[Sub-Cost Package Detail]
    	, [CC Tab].[CC Description]
    	, [COA Tab].[Core/Non-Core]
    	, [LT-Project_Portfolio].[ProjectType]
    FROM tblActuals 
    LEFT JOIN [CC Tab] 
    ON tblActuals.CostCenter = [CC Tab].[Cost Center] 
    LEFT JOIN [COA Tab] 
    ON tblActuals.CostElement = [COA Tab].COA 
    LEFT JOIN [IO Tab] 
    ON tblActuals.IO = [IO Tab].[IO Shortcode] 
    LEFT JOIN [LT-Project_Portfolio] 
    ON [IO Tab].[PFP Alias] = [LT-Project_Portfolio].[Alias]


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 25, 2018 4:44 PM
  • Since the relationships between both columns [IO Tab].[PFP Alias] and [LT-Project_Portfolio].[Alias] not one to one, maybe one to many.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, September 25, 2018 6:06 PM

All replies

  • Hi,

    Is this related to your other question?

    https://social.msdn.microsoft.com/Forums/office/en-US/58ad160b-0c98-4c5d-85b1-f820a26b3ad2/trouble-adding-another-join-can-anyone-see-why-there-is-an-issue?forum=accessdev

    Not sure if double-posting is allowed here.

    Good luck!

    Tuesday, September 25, 2018 3:50 PM
  • You do not need any parentheses in your code. Just remove them.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, September 25, 2018 4:04 PM
  • FROM tblActuals 
    LEFT JOIN [CC Tab] ON tblActuals.CostCenter = [CC Tab].[Cost Center]
    LEFT JOIN [COA Tab] ON tblActuals.CostElement = [COA Tab].[COA]
    LEFT JOIN [IO Tab] ON tblActuals.IO = [IO Tab].[IO Shortcode] 
    LEFT JOIN [LT-Project_Portfolio] ON [IO Tab].[PFP Alias] = [LT-Project_Portfolio].[Alias];

    By the way, it is not good practice to have some spaces in the table names.


    A Fan of SSIS, SSRS and SSAS


    Tuesday, September 25, 2018 4:42 PM
  • Thank you, sir!

    However, removing the parens gave me a "missing operator" error...

    Can you show your exact code after removing parens?

    It should look like below as per your posted query

    SELECT 
    	 [CC Tab].Division
    	, [CC Tab].Function
    	, [CC Tab].[Sub-Function]
    	, [COA Tab].[Account Summary]
    	, [COA Tab].Mapping
    	, [COA Tab].[TM1 Cost Package Name]
    	, [IO Tab].[PFC Alias]
    	, [IO Tab].[PFP Alias]
    	, [IO Tab].IO
    	, [IO Tab].[IO Shortcode]
    	, tblActuals.Scenario
    	, tblActuals.Monthly_Variance_Amount
    	, tblActuals.YTD_Actuals
    	, tblActuals.YTD_Plan
    	, tblActuals.YTD_Var_to_Plan
    	, tblActuals.Plan
    	, tblActuals.Keeper
    	, [CC Tab].[Report Grouping]
    	, tblActuals.RefDocumentNumber
    	, tblActuals.[Aux#acctassignment_1]
    	, tblActuals.CostCenter
    	, tblActuals.CostElement
    	, tblActuals.Cost_element_name
    	, tblActuals.[Val/COAreaCrcy]
    	, tblActuals.Period
    	, tblActuals.Nameofoffsettingaccount
    	, tblActuals.DocumentHeaderText
    	, tblActuals.Purchasing_Document
    	, tblActuals.Purchase_order_text
    	, tblActuals.FiscalYear
    	, tblActuals.UserName
    	, tblActuals.[Ref#companycode]
    	, tblActuals.Name
    	, [COA Tab].[Cost Package]
    	, [COA Tab].[Cost Package Detail]
    	, [COA Tab].[Sub-Cost Package]
    	, [COA Tab].[Sub-Cost Package Detail]
    	, [CC Tab].[CC Description]
    	, [COA Tab].[Core/Non-Core]
    	, [LT-Project_Portfolio].[ProjectType]
    FROM tblActuals 
    LEFT JOIN [CC Tab] 
    ON tblActuals.CostCenter = [CC Tab].[Cost Center] 
    LEFT JOIN [COA Tab] 
    ON tblActuals.CostElement = [COA Tab].COA 
    LEFT JOIN [IO Tab] 
    ON tblActuals.IO = [IO Tab].[IO Shortcode] 
    LEFT JOIN [LT-Project_Portfolio] 
    ON [IO Tab].[PFP Alias] = [LT-Project_Portfolio].[Alias]


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 25, 2018 4:44 PM
  • Since the relationships between both columns [IO Tab].[PFP Alias] and [LT-Project_Portfolio].[Alias] not one to one, maybe one to many.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, September 25, 2018 6:06 PM