Calculations in a Totals Query

[ramudo]

My previous question - How to do calculations on calculated fields - My
question should have been: I want to create a calculated field using two
fields that have been summed in a Totals Query without getting Error 3203 -
"Subqueries cannot be used in the expression <expression>". Thank you.

 

[ramudo]

I am a beginner in the use of Access. This is just a simple Totals query
that does a sum on each of two fields by fuel type and by vendor: a "amount"
field and a "gallons" field. Then, I need a calculated field that finds the
average of "amount" / "gallons". A simple sum of the calculated field does
not give a true average. Thank you. I want to use this query as the source
of a report. Thank you.

 

[vanderghast]

SELECT vendor, SUM(amount)/SUM(gallon)
FROM tableNameHere
GROUP BY vendor


in SQL view, using the real fields name, should work.

Note that SUM(x)/SUM(y) is not the same as AVG(x/y), As example, with

x y
2 1
4 4

SUM(x)/SUM((y) = 6 / 5 = 1.2


on the other hand
x/y = 2
and 1

and the average of (2, 1) = 1.5

and clearly, 1.5 <> 1.2



Vanderghast, Access MVP

 

[ramudo]

Thank you for your help.

Don't have to try to do all in one query, in fact, you can't. Use the Report
builder to set up the report's RecordSource query (SELECT * FROM tablename).
Have textbox in Detail section for the Amount * Gallons along with other
textboxes for other fields and a textbox in the report footer section for the
Avg(Amount / Gallons). Use the report's grouping and sorting features if you
have categories that need to be subtotaled.

I am a beginner in the use of Access. This is just a simple Totals query
that does a sum on each of two fields by fuel type and by vendor: a "amount"
field and a "gallons" field. Then, I need a calculated field that finds the
average of "amount" / "gallons". A simple sum of the calculated field does
not give a true average. Thank you. I want to use this query as the source
of a report. Thank you.

Is this query the RecordSource for a form or report? Can you use a textbox
with ControlSource expression that adds the two fields? I have not used a

[quoted text clipped - 8 lines]

fields that have been summed in a Totals Query without getting Error 3203 -
"Subqueries cannot be used in the expression <expression>". Thank you.

 

[ramudo]

Thank you for your help.

SELECT vendor, SUM(amount)/SUM(gallon)
FROM tableNameHere
GROUP BY vendor


in SQL view, using the real fields name, should work.

Note that SUM(x)/SUM(y) is not the same as AVG(x/y), As example, with

x y
2 1
4 4

SUM(x)/SUM((y) = 6 / 5 = 1.2


on the other hand
x/y = 2
and 1

and the average of (2, 1) = 1.5

and clearly, 1.5 <> 1.2



Vanderghast, Access MVP