Count expression in a Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that calculates averages, etc. for test information. There is
then a field that calculates the average of three fields (OpECT, MidECT, and
DrECT). Here's where I have a problem: When test information is entered,
there may not be a, for example, DrECT for a specific test. So, when the
average is calculated I do NOT want the average of the three fields to be
affected by a 0. Therefore, I was wondering if there is a way to average only
those of the three fields that have a value greater than 0.

For example:
OpECT MidECT DrECT AvgECT
100 95 90 95 ((100+95+90)/3)
55 65 0 60 ((55+65)/2))

(The second one divided by 2 because only 2 of the 3 are greater than 0)

I hope this is not too confusing...I'd appreciate any help! Thanks!
 
What about ---
([MidECT] +[DrECT] + [AvgECT]) / (IIF([MidECT]=0,0,1) + IIF([DrECT]=0,0,1)
+ IIF([AvgECT]=0,0,1))
 
I entered the formula and it works...EXCEPT..for example, there is a line
that ONLY has the OpECT...not MidECT or DrECT...that row's OpECt = 55.8, so I
want the average to return 55.8 since it should divide only by 1, not 3;
however, right now, the average returns 18.6...which is 55.8 divided by 3.

Suggestions?

KARL DEWEY said:
What about ---
([OpECT] +[MidrECT] + [DrECT]) / (IIF([OpECT]=0,0,1) + IIF([MidECT]=0,0,1)
+ IIF([DrECT]=0,0,1))


Kate said:
I have a query that calculates averages, etc. for test information. There is
then a field that calculates the average of three fields (OpECT, MidECT, and
DrECT). Here's where I have a problem: When test information is entered,
there may not be a, for example, DrECT for a specific test. So, when the
average is calculated I do NOT want the average of the three fields to be
affected by a 0. Therefore, I was wondering if there is a way to average only
those of the three fields that have a value greater than 0.

For example:
OpECT MidECT DrECT AvgECT
100 95 90 95 ((100+95+90)/3)
55 65 0 60 ((55+65)/2))

(The second one divided by 2 because only 2 of the 3 are greater than 0)

I hope this is not too confusing...I'd appreciate any help! Thanks!
Click to expand...
Click to expand...
 
nevermind..it works! thanks a BUNCH!

KARL DEWEY said:
What about ---
([MidECT] +[DrECT] + [AvgECT]) / (IIF([MidECT]=0,0,1) + IIF([DrECT]=0,0,1)
+ IIF([AvgECT]=0,0,1))


Kate said:
I have a query that calculates averages, etc. for test information. There is
then a field that calculates the average of three fields (OpECT, MidECT, and
DrECT). Here's where I have a problem: When test information is entered,
there may not be a, for example, DrECT for a specific test. So, when the
average is calculated I do NOT want the average of the three fields to be
affected by a 0. Therefore, I was wondering if there is a way to average only
those of the three fields that have a value greater than 0.

For example:
OpECT MidECT DrECT AvgECT
100 95 90 95 ((100+95+90)/3)
55 65 0 60 ((55+65)/2))

(The second one divided by 2 because only 2 of the 3 are greater than 0)

I hope this is not too confusing...I'd appreciate any help! Thanks!
Click to expand...
Click to expand...
 
Back
Top