Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query with left join giving #Error values

Status
Not open for further replies.

lsridh

Technical User
Joined
Apr 3, 2009
Messages
6
Location
US
I recently migrated an Access 97 database to Access 2003. After figuring out how to get rid of the GUIDs & other such system generated fields, my database is clean now. But some of the queries are not performing correctly. I am using left join on two queries to generate a third. Query 2 has a subset of the records derived based on a Yes/No field. So, when I run the query I get #error values for all records which do not exist in query 2.

Query 2 has Dynamic Loadsize as Yes/No & Dynamic UPH which is a calculated field.

The current query gives #Error for dynamic UPH when Dynami Loadsize is No.
 
Any chance you could post the SQL code(s) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Let me know if I need to explain something

SELECT capRoutes_1.RTS_Area, capRoutes_1.Rev, capRoutes_1.[Tool Size], capRoutes_1.Route, capRoutes_1.Sequence, capRoutes_1.[Mvs Rem], capRoutes_1.[Fiscal Year], capRoutes_1.[Work Week], capRoutes_1.Qty, capRoutes_1.Yields, capRoutes_1.Yld_Qty, capRoutes_1.Oper, capRoutes_1.[Oper Description], capRoutes_1.Area, capRoutes_1.Move, capRoutes_1.[Tool Set], capRoutes_1.[Tool Subset], capRoutes_1.[# of Tools Installed], capRoutes_1.[# of Tools Running], capRoutes_1.Availability, capRoutes_1.Efficiency, capRoutes_1.Utilization, capRoutes_1.Vijay_Util, (1/[UPH])*[yld_qty] AS Prod_Hours, IIf([utilization] Is Null,0.8,[utilization])*[# of tools running]*24*7 AS Avail_Prod_Hours, ((1/[UPH])*[yld_qty])/(IIf([utilization] Is Null,0.8,[utilization])*24*7) AS Tools_Req, capRoutes_1.Recipe_Chem_ID, capRoutes_1.[Recipe Name], IIf([dyn_UPH]=False,[cur_UPH],[Dyn_UPH]) AS UPH, capRoutes_1.Recipe_ID, capDynamicLoad_2.Dyn_UPH, capRoutes_1.Cur_UPH, capRoutes_1.[# of Tools Qual'd On], capRoutes_1.Dynamic_LoadSize, capRoutes_1.[Non-standard], IIf([dynamic_loadsize]=False,[caproutes_1].[Recipe Load Size],[Dyn_ls]) AS Comb_LS, capRoutes_1.[Sub-Area]
FROM capRoutes_1 LEFT JOIN capDynamicLoad_2 ON (capRoutes_1.[Tool Set] = capDynamicLoad_2.[Tool Set]) AND (capRoutes_1.[Recipe Name] = capDynamicLoad_2.[Recipe Name]) AND (capRoutes_1.[Work Week] = capDynamicLoad_2.[Work Week]) AND (capRoutes_1.[Fiscal Year] = capDynamicLoad_2.[Fiscal Year])
ORDER BY capRoutes_1.Route, capRoutes_1.Sequence;
 
Which column(s) get #Error in the above query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
A bunch of them - but the main problem is the UPH column... as that is the one which uses the iif condition with Dyn_UPH.
 
What are all the fields comming from capDynamicLoad_2 in the above query ?

Seems you want to make some math with Null values ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Here is the CapDynamicLoad_2 query

The problem is I think the Dlookup function, which uses dynamic_loadsize which is a Yes/No field on a table. The recipe time for all the #error values is blank, which indicates that there is something going on there. But I am not sure what to fix?


SELECT capDynamicLoad_1.[FY&WW], capDynamicLoad_1.[Fiscal Year], capDynamicLoad_1.[Work Week], capDynamicLoad_1.[Tool Set], capDynamicLoad_1.[Recipe Name], capDynamicLoad_1.Yld_Qty, capDynamicLoad_1.UPH AS Current_UPH, capDynamicLoad_1.[Load Size], capDynamicLoad_1.[Recipe Load Size], capDynamicLoad_1.[Recipe Time], capDynamicLoad_1.[First Wafer], capDynamicLoad_1.[Seq Wafers], capDynamicLoad_1.Overhead, IIf(IIf([dynamic_loadsize]=Yes,DLookUp("[Default_Load_size]","Dynamic_Loadsize"," [Max_WW_Qty]>=" & [yld_qty] & " and [Min_WW_Qty]<=" & [yld_qty] & " and [Max_Recipe_Time]>=" & [Recipe Time]),"")>[recipe load size],[recipe load size],IIf([dynamic_loadsize]=Yes,DLookUp("[Default_Load_size]","Dynamic_Loadsize"," [Max_WW_Qty]>=" & [yld_qty] & " and [Min_WW_Qty]<=" & [yld_qty] & " and [Max_Recipe_Time]>=" & [Recipe Time]),"")) AS Dyn_LS, IIf([first wafer] Is Not Null,[dyn_LS]/((([first wafer]+[overhead]+(([Dyn_LS]-1)*[seq wafers]))/60)),[dyn_LS]/(([recipe time]+[overhead])/60)) AS Dyn_UPH
FROM capDynamicLoad_1
ORDER BY capDynamicLoad_1.[Fiscal Year], capDynamicLoad_1.[Work Week], capDynamicLoad_1.[Tool Set], capDynamicLoad_1.[Recipe Name];
 
The recipe time for all the #error values is blank
Are you saying that you get #Error values in the CapDynamicLoad_2 query too ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I was initially getting error in that -- but by some freak chance (I copied the old Access 97 code to the query) it is working now.

But, I am not sure what changed or why it is working now
 
Was anyone able to figure this out -- pretty please???
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top