inner join query problem

[tchristo]

Hello all,

I've been trying to get hectares (ha) used in a calculation but first it has to be created from a square meters(shape_area) field.
I've tried two options:
1.create ha calculation in initial select statement
2.create ha calculation within an inner join
Neither options work. The first option doesn't seem to allow me to do a nested summation the second option has an error in the inner join but I can't figure out what is the problem.

Thanks for any help you can provide. I've removed asp coding (eg strSQL = strSQL & "....) to try to make it easier to read in this forum.

'''query option 1
SELECT lc.name, eval.ESS_ID, Sum(LC.shape_area * 0.0001) AS Hectares, Sum(eval.[Mean(Ha)]*Sum(LC.shape_area * 0.0001)) AS TotalValue
FROM EcoSysServices_Values as eval INNER JOIN
(LandCover as lc INNER JOIN ESS_to_LULC_LUT as ess ON lc.Cover_ID = ESS.Cover_ID)
ON eval.ESS_ID = ESS.ESS_ID
WHERE lc.name=WashCo
GROUP BY lc.name, eval.ESS_ID

'''query option 2
SELECT lc.name, eval.ESS_ID, Sum(LC.shape_area * 0.0001) AS Hectares, Sum(eval.[Mean(Ha)]*lc.ha) AS TotalValue
FROM EcoSysServices_Values as eval INNER JOIN
(select lc.cover_id, sum(lc.shape_area* 0.0001) as ha from LandCover as lc INNER JOIN ESS_to_LULC_LUT as ess ON lc.Cover_ID = ESS.Cover_ID)
ON eval.ESS_ID = ESS.ESS_ID
WHERE lc.name)=WashCo
GROUP BY lc.name, eval.ESS_ID