I have a query in MSAccess. The results are correct there. I imbed the query in Excel and the results are different. I am using Access and Excel version 10 so I cant use other sources I have to link through the access button as the other way only sees .mdb.
Here is the SQL. The OutPut_Matl.Group field is the only one incorrect.
SELECT First_of_year.FirstOfYear, First_of_year.FirstOfMonth, First_of_year.Division, First_of_year.Order, First_of_year.[Operation #] AS Operation, OutPut_Matl.Description AS Classification, OutPut_Matl.Group, OutPut_Matl.tYPE, OutPut_Matl.SumOfCountOfClassification AS Cnt, OutPut_Matl.Cost1 AS Matl, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Oper_Contr]![oper_cst]/[Count_of_materials]![CountOfDescription],[Total_Oper_Contr]![oper_cst]) AS Contr, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Traffic]![Traffic]/[Count_of_materials]![CountOfDescription],[Total_Traffic]![Traffic]) AS Traf_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_OS]![O-Serv]/[Count_of_materials]![CountOfDescription],[Total_OS]![O-Serv]) AS [O-Serv], IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_surch]![Surch]/[Count_of_materials]![CountOfDescription],[Total_surch]![Surch]) AS Surch, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_oper_lbr]![Labor]/[Count_of_materials]![CountOfDescription],[Total_oper_lbr]![Labor]) AS Lbr_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Operation_Actual_Hours]![ACTUAL_WORK]/[Count_of_materials]![CountOfDescription],[Operation_Actual_Hours]![ACTUAL_WORK]) AS Lbr_hrs
FROM (((((((First_of_year LEFT JOIN OutPut_Matl ON (First_of_year.[Operation #] = OutPut_Matl.[Operation #]) AND (First_of_year.Order = OutPut_Matl.Order)) LEFT JOIN Count_of_materials ON (OutPut_Matl.[Operation #] = Count_of_materials.[Operation #]) AND (OutPut_Matl.Order = Count_of_materials.Order)) LEFT JOIN Operation_Actual_Hours ON (First_of_year.[Operation #] = Operation_Actual_Hours.[Operation #]) AND (First_of_year.Order = Operation_Actual_Hours.Order)) LEFT JOIN Total_Oper_Contr ON (First_of_year.[Operation #] = Total_Oper_Contr.[Operation #]) AND (First_of_year.Order = Total_Oper_Contr.Order)) LEFT JOIN Total_Traffic ON (First_of_year.[Operation #] = Total_Traffic.[Operation #]) AND (First_of_year.Order = Total_Traffic.Order)) LEFT JOIN Total_OS ON (First_of_year.[Operation #] = Total_OS.[Operation #]) AND (First_of_year.Order = Total_OS.Order)) LEFT JOIN Total_surch ON (First_of_year.[Operation #] = Total_surch.[Operation #]) AND (First_of_year.Order = Total_surch.Order)) LEFT JOIN Total_oper_lbr ON (First_of_year.[Operation #] = Total_oper_lbr.[Operation #]) AND (First_of_year.Order = Total_oper_lbr.Order);
Here is the SQL. The OutPut_Matl.Group field is the only one incorrect.
SELECT First_of_year.FirstOfYear, First_of_year.FirstOfMonth, First_of_year.Division, First_of_year.Order, First_of_year.[Operation #] AS Operation, OutPut_Matl.Description AS Classification, OutPut_Matl.Group, OutPut_Matl.tYPE, OutPut_Matl.SumOfCountOfClassification AS Cnt, OutPut_Matl.Cost1 AS Matl, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Oper_Contr]![oper_cst]/[Count_of_materials]![CountOfDescription],[Total_Oper_Contr]![oper_cst]) AS Contr, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_Traffic]![Traffic]/[Count_of_materials]![CountOfDescription],[Total_Traffic]![Traffic]) AS Traf_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_OS]![O-Serv]/[Count_of_materials]![CountOfDescription],[Total_OS]![O-Serv]) AS [O-Serv], IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_surch]![Surch]/[Count_of_materials]![CountOfDescription],[Total_surch]![Surch]) AS Surch, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Total_oper_lbr]![Labor]/[Count_of_materials]![CountOfDescription],[Total_oper_lbr]![Labor]) AS Lbr_cst, IIf([Count_of_materials]![CountOfDescription] Is Not Null,[Operation_Actual_Hours]![ACTUAL_WORK]/[Count_of_materials]![CountOfDescription],[Operation_Actual_Hours]![ACTUAL_WORK]) AS Lbr_hrs
FROM (((((((First_of_year LEFT JOIN OutPut_Matl ON (First_of_year.[Operation #] = OutPut_Matl.[Operation #]) AND (First_of_year.Order = OutPut_Matl.Order)) LEFT JOIN Count_of_materials ON (OutPut_Matl.[Operation #] = Count_of_materials.[Operation #]) AND (OutPut_Matl.Order = Count_of_materials.Order)) LEFT JOIN Operation_Actual_Hours ON (First_of_year.[Operation #] = Operation_Actual_Hours.[Operation #]) AND (First_of_year.Order = Operation_Actual_Hours.Order)) LEFT JOIN Total_Oper_Contr ON (First_of_year.[Operation #] = Total_Oper_Contr.[Operation #]) AND (First_of_year.Order = Total_Oper_Contr.Order)) LEFT JOIN Total_Traffic ON (First_of_year.[Operation #] = Total_Traffic.[Operation #]) AND (First_of_year.Order = Total_Traffic.Order)) LEFT JOIN Total_OS ON (First_of_year.[Operation #] = Total_OS.[Operation #]) AND (First_of_year.Order = Total_OS.Order)) LEFT JOIN Total_surch ON (First_of_year.[Operation #] = Total_surch.[Operation #]) AND (First_of_year.Order = Total_surch.Order)) LEFT JOIN Total_oper_lbr ON (First_of_year.[Operation #] = Total_oper_lbr.[Operation #]) AND (First_of_year.Order = Total_oper_lbr.Order);
