Re: Calculating an average across multiple fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

That means that you have used the avg function in the query and therefor you must group by all the other fields that you are displaying or use one of the other aggregate functions.

SELECT LastName, FirstName,
avg([field1]+[field2]) as AvgOfTotal
FROM [Your Table]
GROUP BY LastName, FirstName


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Sherrie wrote:
When I try to average two fields using the following:
avg([field1]+[field2]) I keep getting following error message:

You have tried to execute a query that does not include the specific expression
'LastName' as part of an aggregate funcion.

LastName is the field name of the first field of the query. Do you know what this means?

"Douglas J. Steele" wrote:

Nz converts Null values to 0.

If you don't do that, your sum will be Null, even if all but one of the fields have values.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Sherrie" <Sherrie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:06B8E3D3-09B2-4CC3-8383-DE786BCBD58F@xxxxxxxxxxxxxxxx
What does the NZ stand for? I understand the AVG and the [field1],[filed2]
and so forth, but does the NZ have any significance? I have been trying to
average multiple fields in a query, but I am not having any luck.
My example is AVG:AVG([Field1],[Field2]). and AVG:AVG([Field1]+[Field2], but
neither one is working for me.

Also can this same calculation be done in a report. I want to average
different fields, but they are not in the same column?

Sherrie

"Arvin Meyer [MVP]" wrote:

There are no ranges in Access, and you apprently have commited "spread***"
on youe table. That is, you have used a field (column) instead of a row to
store the values. Typically, an Access row for your data would look like:

ID Value Description

instead of:

ID Value Value Value Value etc.

Now to answer your question. Add a column in your query, like:

AverageOfFields: AVG(NZ([Field1]+NZ([Field2]+NZ([Field3]+...)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Vicky Sandvig" <VickySandvig@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6E25BEFA-7FBA-4B06-A3C1-3C7829EBB935@xxxxxxxxxxxxxxxx
I have a query that contains multiple fields (16) with number data types. I
want to calculate the averages for the values in those fields. I know you
can
use ranges in Excel, but I haven't been able to figure out the syntax for
using ranges in Access or even if I can use ranges.

Any help would be greatly appreciated. Thank you.




.

Quantcast