Perfomring calculations on rows in a query!

[Guest]

I'm trying to produce a query that will calculate the average value across
five fields in the query ( ie across the "row" of the query).
This seems to be very difficult as the "total" field of the query only
allows you to calculate the average for each single field ( ie in each column
of the query).

To try and illustrate, what I want in the query is:

Field 1 Field 2 Feild 3 Field 4 Field 5 Average
(Calculated Field
-----------------------------------------------------------------------------------
A B C D E
Average of A,B,C,D,E
F G H I J
Average of A,G,H,I,J
etc

But what I get is:
Field 1 Field 2 Feild 3 Field 4 Field 5
-----------------------------------------------------------------------------------
A B C D E

F G H I
J
Average.. ..of A&F ..of B&G ..of C&H ..of D&I ..of E&J

Can somebody tell me how I could get over this?

Many thanks

Phil

 

[Jeff Boyce]

Phil

It is not all that unusual to be summing or averaging across multiple
fields/columns ... if you are working in a spreadsheet! Access is a
relational database.

Access has an aggregate function for averaging. It works on data that is
well-normalized. Hence, the Access function requires the data to be in a
SINGLE field.

You have a couple of choices --
1. if you really only need to average across columns, use a spreadsheet
2. if you have to use Access, and can't change the table design, you'll
have to create your own function

Or, you could consider stepping back from the current data structure and
more-fully normalize your data, so you could make better use of the tools
Access provides.

 

[George Nicholson]

You can't use the built-in aggregate tools for this. Add your own
calculation to the query: MyAverage: ([Field1] + [Field2] + [Field3] +
[Field4] + [Field5]) / 5

Any fields that might contain Nulls will *each* need to wrapped in a NZ
function or any calculation that encounters a Null will return a Null.
(Nz([Field1],0) + Nz([Field2],0) + .... etc

HTH,