INSERT INTO FINISHED_EROSION ( strDistrict, CommonName, strProject, System, prjDescription, Family, Product, CommonProduct, Promo, Project_Start_Date, Finished_Date, Project_End_Date, Finish_Period, Customer_Number, BillTo, SalesType, Scope, ExpandedVM, Face_Price, Total_Actual, Total_Original, Total_Variance, Labor_Variance, Material_Variance, Subcontract_Variance, Sundry_Variance, RegionID, OperationID )
SELECT CTC_FINISHED.strDistrict, DISTRICTS.CommonName, CTC_FINISHED.strProject, CTC_FINISHED.System, PROJECTMASTER.prjDescription, PRODUCT_CODES.FADescription, CTC_FINISHED.Family, PRODUCT_CODES.CommonProduct, IIf(IsNull([Promotion_Code]),"",[Promotion_Code] & " - " & [NationalAccountName]) AS Promo, CTC_FINISHED.Project_Start_Date, CTC_FINISHED.Finished_Date, CTC_FINISHED.Project_End_Date, CTC_FINISHED.Fiscalperiod AS Finish_Period, CTC_FINISHED.Customer_Number, CTC_FINISHED.Customer_Name AS BillTo, SALESTYPE_CODES.SalesType, SCOPE_CODES.ScopeType, VERTICAL_CODES.ExpandedVM, [System_face_price]*[US$Rate] AS Face_Price, [Total_Actual_Costs]*[US$Rate] AS Total_Actual, [Total_original_estimated]*[US$Rate] AS Total_Original, ([Total_original_estimated]-[Total_Actual_Costs])*[US$Rate] AS Total_Variance, ([Labor_original_estimated_dollars]-[Labor_Actual_Dollars])*[US$Rate] AS Labor_Variance, ([Material_Original_estimated_dollar]-[Material_Actual_Cost])*[US$Rate] AS Material_Variance, ([Subcontracting_Original_estimated_dollar]-[Subcontracting_Actual_Cost])*[US$Rate] AS Subcontract_Variance, ([Sundry_Original_estimated_dollar]-[Sundry_Actual_Cost])*[US$Rate] AS Sundry_Variance, DISTRICTS.RegionID, Left([DISTRICTS].[RegionID],2) AS Op
FROM ((((((((CTC_FINISHED INNER JOIN DISTRICTS ON CTC_FINISHED.strDistrict = DISTRICTS.strDistrict) INNER JOIN VERTICAL_CODES ON CTC_FINISHED.Vertical_Market = VERTICAL_CODES.VMID) INNER JOIN PRODUCT_CODES ON CTC_FINISHED.Family = PRODUCT_CODES.ProdID) INNER JOIN REGION ON DISTRICTS.RegionID = REGION.RegionID) INNER JOIN FX ON DISTRICTS.Currency = FX.Currency) INNER JOIN PROJECTMASTER ON CTC_FINISHED.strProject = PROJECTMASTER.strProject) LEFT JOIN NATACCTS ON CTC_FINISHED.Promotion_Code = NATACCTS.NatAcctID) LEFT JOIN SCOPE_CODES ON PROJECTMASTER.[T$SCOP] = SCOPE_CODES.strScope) LEFT JOIN SALESTYPE_CODES ON PROJECTMASTER.strSALESTYPE = SALESTYPE_CODES.strType
WHERE (((CTC_FINISHED.Fiscalperiod)=[Forms]![Home].[cboYYYYMM]) AND ((FX.Year)=Int([Forms!Home.cboYYYYMM]/100)) AND ((Abs([System_face_price])+Abs([Total_Actual_Costs]))>2));
