By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

Having problems creating a Union Query

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TreevNoteData.Account_Primary_Borrower_Tax_ID_Nbr AS TaxID,
  2.              Sum(TreevNoteData.Note_Current_Balance) AS SumOfNote_Current_Balance,
  3.              Sum(TreevNoteData.Note_Ledger_Balance) AS SumOfNote_Ledger_Balance,
  4.              Sum(TreevNoteData.Note_Appraisal_Value) AS SumOfNote_Appraisal_Value,
  5. 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],
  6. 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,
  7. Sum(IIf([TreevNoteData]![Note_Type_Code] In ("100","101","190","402"),[TreevNoteData]![Note_Ledger_Balance],0)) AS ComBal,
  8.              NULL,
  9.              NULL
  10. FROM TreevNoteData 
  11. UNION ALL
  12. SELECT TreevIndirectData.Endr_Customer_Tax_ID_Number AS TaxID,
  13.              Sum(TreevIndirectData.Indirect_Liab_Limit_Amount) AS SumOfIndirect_Liab_Limit_Amount, 
  14.              Sum(TreevIndirectData.Indirect_Liab_Limit_Percent) AS SumOfIndirect_Liab_Limit_Percent, 
  15.              Sum(TreevIndirectData.Note_Cmmt_Bank_Share_Ledger_Balance) AS SumOfNote_Cmmt_Bank_Share_Ledger_Balance,
  16.              Sum(TreevIndirectData.Note_Cmmt_Current_Balance) AS SumOfNote_Cmmt_Current_Balance, 
  17.              Sum(TreevIndirectData.Note_Cmmt_Ledger_Balance) AS SumOfNote_Cmmt_Ledger_Balance,
  18.              Sum(TreevIndirectData.Note_Cmmt_Original_Credit_Line_Amt) AS SumOfNote_Cmmt_Original_Credit_Line_Amt, 
  19.              Sum(TreevIndirectData.Note_Cmmt_Unused_Credit_Line_Amount) AS SumOfNote_Cmmt_Unused_Credit_Line_Amount, 
  20.              Sum(TreevIndirectData.Note_Cmmt_Used_Credit_Line_Amount) AS SumOfNote_Cmmt_Used_Credit_Line_Amount FROM TreevIndirectData
  21. GROUP BY TaxID;
Mar 29 '10 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,492
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.
Mar 29 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.