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.

Robert Kemp wrote:
> 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

Dia 's Muire duit

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. (:->)

"Joseph Meehan" <(E-Mail Removed)> wrote in message
news:nAQ2h.23636$(E-Mail Removed)...
> Robert Kemp wrote:
>> 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
>
> Dia 's Muire duit
>
>
>

David F Cox wrote:
> 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

Dia 's Muire duit

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.


"Joseph Meehan" <(E-Mail Removed)> wrote in message
news:J9%2h.22791$(E-Mail Removed)...
> David F Cox wrote:
>> 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
>
> Dia 's Muire duit
>
>
>
>

David F Cox wrote:
> 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.


--
Joseph Meehan

Dia 's Muire duit