Dropping Zeros from Avg

A

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
 
J

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.
 
M

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.
 
A

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
 
J

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>

AlCamp said:
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 said:
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>
 
A

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

Jeff Boyce said:
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>

AlCamp said:
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
 
J

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

AlCamp said:
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

Jeff Boyce said:
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>

AlCamp said:
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
 
A

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

Jeff Boyce said:
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

AlCamp said:
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
 
J

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>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top