I believe this gets you where you need to be. It's untested obviously as I don't have equivalent schema/test data in my system.

the use of the DRv is just to allow us to re-use the data in multiple places, since unlike in access you can't use the column name at the same level you define it.

SELECT

t.TreeID,

t.TreeSort,

t.ContractNo,

t.Species,

s.Species AS Tree,

t.TreeQty,

t.Location,

t.Landmark,

t.PlantHealth,

t.DBH,

t.Height,

t.BD,

t.Buttress,

t.FGOther,

t.FGOtherNotes,

t.SMTrunk,

t.Distance,

t.EquipAccess,

t.TTvsB,

t.Haz_PI,

t.Haz_EC,

t.Haz_Pests,

t.Equip_Bobcat,

t.Equip_Bobcat_Cost,

t.Equip_Crane,

t.Equip_CraneHrs,

t.Equip_Crane_Cost,

t.Equip_Lift,

t.Equip_LiftID,

t.Equip_Lift_Cost,

t.Equip_Lift_Time,

t.Equip_Stump,

t.Equip_Stump_Cost,

t.GM,

t.A,

t.Aa,

t.At,

t.Gl,

t.WorkHrs,

Round((([ArbCosts]+[VehCost]+40)/(1-[GM])*[WorkHrs])+([Equip_CraneHrs]*[Equip_Crane_Cost])+([Equip_Lift_Cost]*[Equip_Lift_Time]+[MaterialsCost]),0) AS SuggTreeCost,

t.ProposalTreeCost,

t.ConfirmationDate,

t.Veh_CTC,

t.Veh_CTC_Cost,

t.Veh_PT,

t.Veh_PT_Cost,

t.Veh_1T,

t.Veh_1T_Cost,

t.Veh_Int,

t.Veh_Int_Cost,

drv.ACost * t.[A] AS ACost,

drv.AaCost * t.[Aa] AS AaCost,

drv.AtCost * [At] AS AtCost,

drv.GlCost * [Gl] AS GlCost,

(drv.[ACost] * t.a) + ( drv.[AaCost] * t.Aa) + (drv.[AtCost] * t.at) + (drv.[GlCost] * t.gl) AS ArbCosts,

([Equip_Bobcat_Cost]+[Equip_Stump_Cost]+[Veh_CTC_Cost]+[Veh_PT_Cost]+[Veh_1T_Cost]+[Veh_Int_Cost]) AS VehCost,

t.HazNotes,

t.OnProposal,

t.CustObj,

t.CustObjDesc,

t.TOWID,

t.Transfer,

t.Timing,

t.Equip_Lift_Time,

t.MissDig,

t.MissDigNo,

t.Haz_Poop,

t.Grouping,

t.GroupedCost,

t.MaterialsCost,

t.Materials,

[Equip_CraneHrs]*[Equip_Crane_Cost] AS CraneCosting,

t.MethodNotes,

t.InvoiceNo

FROM

tblSpecies AS s

INNER JOIN

tblTrees AS t

ONs.SpeciesID = t.Species

CROSS JOIN

(SELECT

( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'A') AS Acost,

( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'Aa') AS AaCost,

( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'At') AS AtCost,

( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'Gl') AS GlCost

) AS drv