Having problems creating a Union Query

[joshtate2001]

I have been trying to figure out how to create a Union Query between two tables with different data with one field containing similiar data.


I have 2 tables:

NoteData & IndirectData

The common field (although named differently in the two tables) contains TaxID information. I am triying to do Sum calculations at the same time in the qury and that may be my probelm. See below:

SELECT TreevNoteData.Account_Primary_Borrower_Tax_ID_Nbr AS TaxID,
             Sum(TreevNoteData.Note_Current_Balance) AS SumOfNote_Current_Balance,
             Sum(TreevNoteData.Note_Ledger_Balance) AS SumOfNote_Ledger_Balance,
             Sum(TreevNoteData.Note_Appraisal_Value) AS SumOfNote_Appraisal_Value,
Sum(IIf(Len([TreevNoteData]![Note_Collateral_Code])=2,IIf([TreevNoteData]![Note_Collateral_Code]="A1",[TreevNoteData]![Note_Ledger_Balance],0),[TreevNoteData]![Note_Ledger_Balance])) AS [Unsecured Bal],
Sum(IIf([TreevNoteData]![Note_Type_Code] In ("100","101","190","402"),IIf(Len([TreevNoteData]![Note_Collateral_Code])=2,IIf([TreevNoteData]![Note_Collateral_Code]="A1",[TreevNoteData]![Note_Ledger_Balance],0),[TreevNoteData]![Note_Ledger_Balance]),0)) AS ComUnsecBal,
Sum(IIf([TreevNoteData]![Note_Type_Code] In ("100","101","190","402"),[TreevNoteData]![Note_Ledger_Balance],0)) AS ComBal,
             NULL,
             NULL
FROM TreevNoteData 
UNION ALL
SELECT TreevIndirectData.Endr_Customer_Tax_ID_Number AS TaxID,
             Sum(TreevIndirectData.Indirect_Liab_Limit_Amount) AS SumOfIndirect_Liab_Limit_Amount, 
             Sum(TreevIndirectData.Indirect_Liab_Limit_Percent) AS SumOfIndirect_Liab_Limit_Percent, 
             Sum(TreevIndirectData.Note_Cmmt_Bank_Share_Ledger_Balance) AS SumOfNote_Cmmt_Bank_Share_Ledger_Balance,
             Sum(TreevIndirectData.Note_Cmmt_Current_Balance) AS SumOfNote_Cmmt_Current_Balance, 
             Sum(TreevIndirectData.Note_Cmmt_Ledger_Balance) AS SumOfNote_Cmmt_Ledger_Balance,
             Sum(TreevIndirectData.Note_Cmmt_Original_Credit_Line_Amt) AS SumOfNote_Cmmt_Original_Credit_Line_Amt, 
             Sum(TreevIndirectData.Note_Cmmt_Unused_Credit_Line_Amount) AS SumOfNote_Cmmt_Unused_Credit_Line_Amount, 
             Sum(TreevIndirectData.Note_Cmmt_Used_Credit_Line_Amount) AS SumOfNote_Cmmt_Used_Credit_Line_Amount FROM TreevIndirectData
GROUP BY TaxID;

[NeoPa]

I suggest you find a cut-down version which exhibits the same symptoms that you want help with. Most of this SQL is entirely unrelated to your question so expecting others to wade through this just to find your problem is not reasonable.