Calculations in a Totals Query

  • Thread starter Thread starter ramudo
  • Start date Start date
R

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.
 
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.
 
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
 
Thank you for your help.

June7 via AccessMonster.com said:
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
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your help.

vanderghast said:
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
Click to expand...
 
Back
Top