SELECT da_ligne.RFQ_NATURE, sum(da_ligne.Total_Price) as Total FROM (SELECT da_ligne.C_DA, da_ligne.C_REF, da_ligne.RFQ_PRICE, da_ligne.RFQ_CURENC, da_ligne.RFQ_RATE, da_ligne.RFQ_TOOLS, da_ligne.RFQ_NATURE, ((([RFQ_PRICE]*[RFQ_RATE])*[Q_DEM])+([RFQ_TOOLS]*[RFQ_RATE])) AS Total_Price
Where da_ligne.C_DA="37744" and da_ligne.RFQ_NATURE="MG");
In the above query, Total_Price is a calculated field.
I want to get the total of the this calculated field as Total in the same query.
just glancing quickly at your SQL string, two things stand out:
1) you don't have a GROUP BY clause, so the query doesn't know how to group the data before calculating the sum
2) if you want to do the sum on a calculated value - an "Expression" (which you are trying to do) you need to put the expression in the Sum function (not the Expression name) because the Expression name doesn't exist at the time the query calculates - it only exists in the result. Sometimes you can get around this in SQL server, but not in Access.
One (of several) way around point 2 is to use a select query first that calculates the "totals" expression, then in the subsequent query, group by and sum the expression.