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

Left Join Returning #Error

P: 10
I have a query that I'm trying to return all Projects and the budget for any of those projects that exist. If a budget doesn't exist, I want to show 0s. For some reason, I can't get rid of #Error for these fields when the left join doesn't return something. Below is my query with all of the different attempts of handling this issue:

Expand|Select|Wrap|Line Numbers
  1. SELECT Project.UCMG_CD, 
  2.     Project.SPRF, 
  3.     Project.PROJECT_NAME, 
  4.     iif(isnull(BudgetImport_IDSplit.Current_Prompt_Estimate),0,BudgetImport_IDSplit.Current_Prompt_Estimate) AS Current_Prompt_Estimate, 
  5.     nz(BudgetImport_IDSplit.Hist_Spend,0) AS Hist_Spend,
  6.  BudgetImport_IDSplit.Current_Prompt_Estimate-BudgetImport_IDSplit.Hist_Spend AS CY_Approve_UHGIT_Budget, 
  7.     iif(iserror(BudgetImport_IDSplit.CY_UHGIT_Spend),0,BudgetImport_IDSplit.CY_UHGIT_Spend) AS CY_UHGIT_Spend,
  8.     BudgetImport_IDSplit.CY_Remain_Spend AS CY_Remain_Spend,
  9.    BudgetImport_IDSplit.CY_UHGIT_Spend+BudgetImport_IDSplit.CY_Remain_Spend AS CY_Total_Proj_Spend
  10. FROM Project 
  11. LEFT JOIN BudgetImport_IDSplit 
  12.   ON Project.SPRF = BudgetImport_IDSplit.SPRF;
  13.  
I've attached a screen shot of the data the query is returning. Any ideas?


Thanks

Joe
Attached Images
File Type: jpg left_join_errors.jpg (61.0 KB, 1580 views)
Oct 13 '12 #1
Share this Question
Share on Google+
3 Replies


P: 10
Sorry for the double post, but I just found some interesting information. Sounds like this is caused because I'm left joining with a query. With this being the case, here's the SQL behind the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(Len(BudgetImport.F1)>9,
  2. BudgetImport.F1,Null) AS UCMG_ID, 
  3.   IIf(Len(BudgetImport.F1)=9,
  4. BudgetImport.F1,Null) AS SPRF, 
  5.   ccur(nz(BudgetImport.F4,0)) AS CY_UHG_IT_Alloc, 
  6.   ccur(BudgetImport.F7) AS Current_Prompt_Estimate, 
  7.   ccur(iif(BudgetImport.F9='$-',0,BudgetImport.F9)) AS Hist_Spend, 
  8.   ccur(BudgetImport.F10) AS CY_UHGIT_Spend, 
  9.   ccur(iif(BudgetImport.F12='$-',0,BudgetImport.F12)) AS CY_Remain_Spend
  10. FROM BudgetImport;
  11.  
The reason for the IIF translating the $- value is that this table is populated with an import from Excel. For this one particular column, 0s were shown as a $- so the CCUR translation was erroring out.

For now, I'm going to try to re-write the query to include this basic information from Project to see if it works better. If anyone has any better ideas, I'm all ears :).

Thanks

Joe
Oct 13 '12 #2

P: 10
Well, I solved my own issue, but it's not the solution I wanted. I created a query with the following SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT Project.UCMG_CD, Project.SPRF, Project.PROJECT_NAME, IIF(ISNULL(BudgetImport.F7),0,CCur(BudgetImport.F7)) AS Current_Prompt_Estimate, IIF(ISNULL(BudgetImport.F9),0,CCur(IIf(BudgetImport.F9='$-',0,BudgetImport.F9))) AS Hist_Spend, IIF(ISNULL(BudgetImport.F10),0,CCur(BudgetImport.F10)) AS CY_UHGIT_Spend, IIF(ISNULL(BudgetImport.F12),0,CCur(IIf(BudgetImport.F12='$-',0,BudgetImport.F12))) AS CY_Remain_Spend
  2. FROM Project LEFT JOIN BudgetImport ON Project.SPRF = BudgetImport.F1
  3. WHERE Project.SPRF NOT IN ('TBD','N/A');
  4.  
I then changed my report query to this:

Expand|Select|Wrap|Line Numbers
  1. SELECT Project.UCMG_CD, Project.SPRF, Project.PROJECT_NAME, Budget_IT_ByProject.Current_Prompt_Estimate, Budget_IT_ByProject.Hist_Spend, Budget_IT_ByProject.Current_Prompt_Estimate-Budget_IT_ByProject.Hist_Spend AS CY_Approve_UHGIT_Budget, Budget_IT_ByProject.CY_UHGIT_Spend, Budget_IT_ByProject.CY_UHGIT_Spend+Budget_IT_ByProject.CY_Remain_Spend AS CY_Total_Proj_Spend
  2. FROM Project LEFT JOIN Budget_IT_ByProject ON Project.SPRF = Budget_IT_ByProject.SPRF;
  3.  
The lesson learned here is that when you left join to a query, Access might not like it and throw an error on any column in the query that isn't returned by the join.

Thanks

Joe
Oct 13 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
The reason for the IIF translating the $- value is that this table is populated with an import from Excel. For this one particular column, 0s were shown as a $- so the CCUR translation was erroring out.
You answered your question.
It is not the left joins that were the issue; instead, it was the incomming data. Anytime you get a "#ERROR" you need to go back to the underlying data and verify that it is correct for the function(s) being used or that there isn't some division by zero that crept in.
Oct 13 '12 #4

Post your reply

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