Calculating an average across multiple fields

[Guest]

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.

 

[Arvin Meyer [MVP]]

There are no ranges in Access, and you apprently have commited "spreadsheet"
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]+...)

 

[Guest]

Arvin,

Thank you for your quick response; I guess I will have to do it the hard way
afterall. To clarify a little bit, each field containing a number value
corresponds to a question on an evaluation form using the Likert scale.

Sincerely,

Vicky Sandvig

There are no ranges in Access, and you apprently have commited "spreadsheet"
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

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.

 

[John Spencer]

I am a bit confused.
Are you trying to get the average
-- of each field (all records in table) one average per question
-- of each record (all fields in the record) one average per respondent
-- of all the fields in each record added together? one average for the
survey

The first is simple to do. Just use an aggregate or "total" query.
The second is a pain to do unless you restructure your data - but I have a
vba routine that will do it without restructuring the data.
The last would be the solution Arvin Meyer posted.

Post back if you need further information/guidance
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Arvin,

Thank you for your quick response; I guess I will have to do it the hard
way
afterall. To clarify a little bit, each field containing a number value
corresponds to a question on an evaluation form using the Likert scale.

Sincerely,

Vicky Sandvig

There are no ranges in Access, and you apprently have commited
"spreadsheet"
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

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.

 

[Sherrie]

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

There are no ranges in Access, and you apprently have commited "spreadsheet"
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

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.

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

There are no ranges in Access, and you apprently have commited
"spreadsheet"
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

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.

 

[Sherrie]

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?

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

(no private e-mails, please)

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

There are no ranges in Access, and you apprently have commited
"spreadsheet"
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

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.

 

[John Spencer]

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
'====================================================

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?

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

(no private e-mails, please)

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

:

There are no ranges in Access, and you apprently have commited
"spreadsheet"
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

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.