Average in a group of fields

[Guest]

What would the formula be to set up one field to add up and average 12 other
fields?
I tried Avg([field 1]+[field 2]+[field 3]+[field 3]+[etc....])
It would not give me an average. This was done in both query and form.

 

[Joseph Meehan]

What would the formula be to set up one field to add up and average
12 other fields?
I tried Avg([field 1]+[field 2]+[field 3]+[field 3]+[etc....])
It would not give me an average. This was done in both query and form.

NORMALIZATION

Your problem is the table design. When you have a group of files with
similar repeated data, it is almost 100% certain that you have committed the
database sin of spreadsheet.

You need to have a separate table to hold all those repeat fields with a
parent child relationship with the rest of the data. Now is the time to fix
it. Otherwise you will just run into more problem. Once corrected it will
be easier to use, faster and smaller.

 

[David F Cox]

Quite right, but in the world of kludge and deadlines:

([field1]+[field2]+[field3]+...+[field12])/12

but, really, you knew that, didn't you?

Avg() is for people that design their databases right. ->)

What would the formula be to set up one field to add up and average
12 other fields?
I tried Avg([field 1]+[field 2]+[field 3]+[field 3]+[etc....])
It would not give me an average. This was done in both query and form.

NORMALIZATION

Your problem is the table design. When you have a group of files with
similar repeated data, it is almost 100% certain that you have committed
the database sin of spreadsheet.

You need to have a separate table to hold all those repeat fields with
a parent child relationship with the rest of the data. Now is the time to
fix it. Otherwise you will just run into more problem. Once corrected it
will be easier to use, faster and smaller.

 

[Joseph Meehan]

Quite right, but in the world of kludge and deadlines:

([field1]+[field2]+[field3]+...+[field12])/12

but, really, you knew that, didn't you?

Avg() is for people that design their databases right. ->)

Yes, there usually are ways around poor design.

Of course it should be noted that the formula will not work IF some of
the twelve fields are empty and IF the desired answer is to be based on the
number of active fields. Then again, the formula could be adjusted and made
more complex to account for empty fields.

 

[David F Cox]

You are quite right, I should have pointed that out.
OTOH if I had posted that it would be me dreading "How would I do that,
then?" ->)

The point is that the built in functions are designed to work with the
quirks of data, and proper normalised design reaps huge benefits in
versatility and ease of use, and I have the "scars" to prove it.

Quite right, but in the world of kludge and deadlines:

([field1]+[field2]+[field3]+...+[field12])/12

but, really, you knew that, didn't you?

Avg() is for people that design their databases right. ->)

Yes, there usually are ways around poor design.

Of course it should be noted that the formula will not work IF some of
the twelve fields are empty and IF the desired answer is to be based on
the number of active fields. Then again, the formula could be adjusted
and made more complex to account for empty fields.

 

[Joseph Meehan]

You are quite right, I should have pointed that out.
OTOH if I had posted that it would be me dreading "How would I do
that, then?" ->)

The point is that the built in functions are designed to work with the
quirks of data, and proper normalised design reaps huge benefits in
versatility and ease of use, and I have the "scars" to prove it.

So do I. I suspect that is true of everyone with any serious experience
with relational databases. It is also why we tend to be evangelistic about
it.