Dropping Zeros from Avg

[AlCamp]

I have 6 values (S1 - S6). Some of these fields have values, and some
contain 0 (zero).
I need to average these values... NOT including any zero values.

In my query behind the form, I place 6 fields like this...
S1Count : IIF(S1=0,0,1)
S2Count : IIF(S2=0,0,1)... etc for all 6

Now I can get my average with...
S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count

Works fine, but... isn't there an easier way?

Thanks in advance,
Al Camp

 

[Jeff Boyce]

One way would be to consider re-defining your data (table) structure. Your
description matches what you'd do if you were using a spreadsheet, but the
relational database approach would be to use one field for the value, and a
second field to "define" it (you've used one field each to define).

Access has some very good functions and tools, but they don't work if you
don't organize your data relationally.

 

[mscertified]

Just add all the values and divide by the number that are not zero.
The expression (S1>0) should return 1 (true) if the value if greater than
zero and 0 (false) otherwise.
An average normally includes all values not just those that are not zero.

 

[AlCamp]

Jeff,
Couldn't disagree more...
These are 6 distinct , unrelated, and disparate values within one record,
NOT a "real" one to many relationship. There will never be more than these
six separate category values, and there are no other calculations against
these values.
The client and I decided that for just 6 values we wouldn't add another
table ( with [SValue] and [SNumber] fields as you suggest) and another
subform (that would require 2 entered values in each record... rather than
just one).
I'm well aware of the rules of Normalization, but I'm not going to be a
slave to them.

but they don't work if you
don't organize your data relationally.

Well, I'm sorry that my table design doesn't meet your high standards!

Since you weren't able to help me with my averaging question... if I
figure out a better method, I'll be sure to pass it along.

Al Camp

 

[Jeff Boyce]

Al

I tried to interpret from your description what you might be dealing with.
I wasn't intending to disrespect your design or your knowledge. And if you
knew me better, the "high standards" comment wouldn't have been applicable.

For example, if you are trying to take the average of 6 values, this, to me,
implies that there is something in common about those values. Otherwise, I
have trouble imagining what an "average" would mean. This is what I was
suggesting.

I, too, try not to be a slave to normalization, and while there are "rules",
I believe each situation is different. Perhaps if you provide a bit more
information about your situation, the 'group can offer a better
suggestion...

Good luck!

Jeff Boyce
<Access MVP>

Jeff,
Couldn't disagree more...
These are 6 distinct , unrelated, and disparate values within one record,
NOT a "real" one to many relationship. There will never be more than these
six separate category values, and there are no other calculations against
these values.
The client and I decided that for just 6 values we wouldn't add another
table ( with [SValue] and [SNumber] fields as you suggest) and another
subform (that would require 2 entered values in each record... rather than
just one).
I'm well aware of the rules of Normalization, but I'm not going to be a
slave to them.

but they don't work if you
don't organize your data relationally.

Well, I'm sorry that my table design doesn't meet your high standards!

Since you weren't able to help me with my averaging question... if I
figure out a better method, I'll be sure to pass it along.

Al Camp

One way would be to consider re-defining your data (table) structure.
Your
description matches what you'd do if you were using a spreadsheet, but the
relational database approach would be to use one field for the value, and
a
second field to "define" it (you've used one field each to define).

Access has some very good functions and tools, but they don't work if you
don't organize your data relationally.

--
Good luck

Jeff Boyce
<Access MVP>

 

[AlCamp]

Thanks Jeff,
I guess I did overreact a bit. I apologize.
Sometimes we read a "tone" into someone's response that isn't really
there. It's one of the shortcomings of email "conversations."

As I said, I do understand what you are suggesting. You would create a
related table with 2 fields [SValue] (a numerical value to be averaged) and
[SType] (ex. entries "S1" thru "S6"). Then, of course, this whole
"non-zero" averaging would be much simpler.
I think this type of "flat" relationship is more appropriate where there
are only 2 or 3 values, and that 6 values is pushing the "non-normalization"
a bit, but we really didn't want to add a table and subform for just this
simple calculation. Also, because I didn't have a subform, I was able to
design a more user friendly "one line" continuous form (with S1-S6 values
displayed horizontally) for user entry.

I realize that my design is a bit odd... so I shouldn't be surprised when
someone says, "Hey... that's odd."

Thanks,
Al Camp

Al

I tried to interpret from your description what you might be dealing with.
I wasn't intending to disrespect your design or your knowledge. And if
you
knew me better, the "high standards" comment wouldn't have been
applicable.

For example, if you are trying to take the average of 6 values, this, to
me,
implies that there is something in common about those values. Otherwise,
I
have trouble imagining what an "average" would mean. This is what I was
suggesting.

I, too, try not to be a slave to normalization, and while there are
"rules",
I believe each situation is different. Perhaps if you provide a bit more
information about your situation, the 'group can offer a better
suggestion...

Good luck!

Jeff Boyce
<Access MVP>

Jeff,
Couldn't disagree more...
These are 6 distinct , unrelated, and disparate values within one record,
NOT a "real" one to many relationship. There will never be more than these
six separate category values, and there are no other calculations against
these values.
The client and I decided that for just 6 values we wouldn't add
another
table ( with [SValue] and [SNumber] fields as you suggest) and another
subform (that would require 2 entered values in each record... rather
than
just one).
I'm well aware of the rules of Normalization, but I'm not going to be
a
slave to them.

but they don't work if you
don't organize your data relationally.

Well, I'm sorry that my table design doesn't meet your high standards!

Since you weren't able to help me with my averaging question... if I
figure out a better method, I'll be sure to pass it along.

Al Camp

message

One way would be to consider re-defining your data (table) structure.
Your
description matches what you'd do if you were using a spreadsheet, but the
relational database approach would be to use one field for the value, and
a
second field to "define" it (you've used one field each to define).

Access has some very good functions and tools, but they don't work if you
don't organize your data relationally.

--
Good luck

Jeff Boyce
<Access MVP>

I have 6 values (S1 - S6). Some of these fields have values, and
some
contain 0 (zero).
I need to average these values... NOT including any zero values.

In my query behind the form, I place 6 fields like this...
S1Count : IIF(S1=0,0,1)
S2Count : IIF(S2=0,0,1)... etc for all 6

Now I can get my average with...
S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count

Works fine, but... isn't there an easier way?

Thanks in advance,
Al Camp

 

[Jeff Boyce]

Al

From your responses, you already are aware that the "flat" design will cause
you problems if there's ever a change in how many "S"s you have. If you are
confident that you will always have six (or fewer), you could consider
setting a reference to the Excel function that does averages, and building a
function in Access that calls that Excel function, passing in the "S1",
"S2", ... values.

Good luck

Jeff

Thanks Jeff,
I guess I did overreact a bit. I apologize.
Sometimes we read a "tone" into someone's response that isn't really
there. It's one of the shortcomings of email "conversations."

As I said, I do understand what you are suggesting. You would create a
related table with 2 fields [SValue] (a numerical value to be averaged) and
[SType] (ex. entries "S1" thru "S6"). Then, of course, this whole
"non-zero" averaging would be much simpler.
I think this type of "flat" relationship is more appropriate where there
are only 2 or 3 values, and that 6 values is pushing the "non-normalization"
a bit, but we really didn't want to add a table and subform for just this
simple calculation. Also, because I didn't have a subform, I was able to
design a more user friendly "one line" continuous form (with S1-S6 values
displayed horizontally) for user entry.

I realize that my design is a bit odd... so I shouldn't be surprised when
someone says, "Hey... that's odd."

Thanks,
Al Camp

Al

I tried to interpret from your description what you might be dealing with.
I wasn't intending to disrespect your design or your knowledge. And if
you
knew me better, the "high standards" comment wouldn't have been
applicable.

For example, if you are trying to take the average of 6 values, this, to
me,
implies that there is something in common about those values. Otherwise,
I
have trouble imagining what an "average" would mean. This is what I was
suggesting.

I, too, try not to be a slave to normalization, and while there are
"rules",
I believe each situation is different. Perhaps if you provide a bit more
information about your situation, the 'group can offer a better
suggestion...

Good luck!

Jeff Boyce
<Access MVP>

Jeff,
Couldn't disagree more...
These are 6 distinct , unrelated, and disparate values within one record,
NOT a "real" one to many relationship. There will never be more than these
six separate category values, and there are no other calculations against
these values.
The client and I decided that for just 6 values we wouldn't add
another
table ( with [SValue] and [SNumber] fields as you suggest) and another
subform (that would require 2 entered values in each record... rather
than
just one).
I'm well aware of the rules of Normalization, but I'm not going to be
a
slave to them.

but they don't work if you
don't organize your data relationally.

Well, I'm sorry that my table design doesn't meet your high standards!

Since you weren't able to help me with my averaging question... if I
figure out a better method, I'll be sure to pass it along.

Al Camp

message
One way would be to consider re-defining your data (table) structure.
Your
description matches what you'd do if you were using a spreadsheet,

but
the

relational database approach would be to use one field for the value, and
a
second field to "define" it (you've used one field each to define).

Access has some very good functions and tools, but they don't work if you
don't organize your data relationally.

--
Good luck

Jeff Boyce
<Access MVP>

I have 6 values (S1 - S6). Some of these fields have values, and
some
contain 0 (zero).
I need to average these values... NOT including any zero values.

In my query behind the form, I place 6 fields like this...
S1Count : IIF(S1=0,0,1)
S2Count : IIF(S2=0,0,1)... etc for all 6

Now I can get my average with...
S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count

Works fine, but... isn't there an easier way?

Thanks in advance,
Al Camp

 

[AlCamp]

Jeff,
My client did agree to this structure, and are aware that if they renege
later, they will "pay the piper."

setting a reference to the Excel function that does averages...

That's a good suggestion. I've never tried calling to an Excel function
to handle a calculation I need in Access. I'll check that out.

To be honest though, my...
S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count
works just fine, and the client likes it, so we'll probably stick with that.
No use reinventing the wheel...

Thanks for all your help.
Al Camp

Al

From your responses, you already are aware that the "flat" design will
cause
you problems if there's ever a change in how many "S"s you have. If you
are
confident that you will always have six (or fewer), you could consider
setting a reference to the Excel function that does averages, and building
a
function in Access that calls that Excel function, passing in the "S1",
"S2", ... values.

Good luck

Jeff

Thanks Jeff,
I guess I did overreact a bit. I apologize.
Sometimes we read a "tone" into someone's response that isn't really
there. It's one of the shortcomings of email "conversations."

As I said, I do understand what you are suggesting. You would create
a
related table with 2 fields [SValue] (a numerical value to be averaged) and
[SType] (ex. entries "S1" thru "S6"). Then, of course, this whole
"non-zero" averaging would be much simpler.
I think this type of "flat" relationship is more appropriate where there
are only 2 or 3 values, and that 6 values is pushing the "non-normalization"
a bit, but we really didn't want to add a table and subform for just this
simple calculation. Also, because I didn't have a subform, I was able to
design a more user friendly "one line" continuous form (with S1-S6 values
displayed horizontally) for user entry.

I realize that my design is a bit odd... so I shouldn't be surprised when
someone says, "Hey... that's odd."

Thanks,
Al Camp

message

Al

I tried to interpret from your description what you might be dealing with.
I wasn't intending to disrespect your design or your knowledge. And if
you
knew me better, the "high standards" comment wouldn't have been
applicable.

For example, if you are trying to take the average of 6 values, this,
to
me,
implies that there is something in common about those values. Otherwise,
I
have trouble imagining what an "average" would mean. This is what I
was
suggesting.

I, too, try not to be a slave to normalization, and while there are
"rules",
I believe each situation is different. Perhaps if you provide a bit more
information about your situation, the 'group can offer a better
suggestion...

Good luck!

Jeff Boyce
<Access MVP>

Jeff,
Couldn't disagree more...
These are 6 distinct , unrelated, and disparate values within one
record,
NOT a "real" one to many relationship. There will never be more than
these
six separate category values, and there are no other calculations against
these values.
The client and I decided that for just 6 values we wouldn't add
another
table ( with [SValue] and [SNumber] fields as you suggest) and another
subform (that would require 2 entered values in each record... rather
than
just one).
I'm well aware of the rules of Normalization, but I'm not going to be
a
slave to them.

but they don't work if you
don't organize your data relationally.

Well, I'm sorry that my table design doesn't meet your high standards!

Since you weren't able to help me with my averaging question... if I
figure out a better method, I'll be sure to pass it along.

Al Camp

message
One way would be to consider re-defining your data (table)
structure.
Your
description matches what you'd do if you were using a spreadsheet, but
the
relational database approach would be to use one field for the
value,
and
a
second field to "define" it (you've used one field each to define).

Access has some very good functions and tools, but they don't work
if
you
don't organize your data relationally.

--
Good luck

Jeff Boyce
<Access MVP>

I have 6 values (S1 - S6). Some of these fields have values, and
some
contain 0 (zero).
I need to average these values... NOT including any zero values.

In my query behind the form, I place 6 fields like this...
S1Count : IIF(S1=0,0,1)
S2Count : IIF(S2=0,0,1)... etc for all 6

Now I can get my average with...
S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count

Works fine, but... isn't there an easier way?

Thanks in advance,
Al Camp

 

[Jeff Boyce]

Al

Your expression:

S1+S2+S3+S4+S5+S6 / S1Count+S2Count+S3Count+S4Count+S5Count+S6Count

doesn't appear handle a Null. The only "enhancement" you may wish to
consider would use the Nz() function as needed.

Jeff Boyce
<Access MVP>