Overflow

S

Stuart

I have 100 or so fields in a Query whose values come from
calculations in a couple other Querys. I Need the
averages for all these fields. I used the AVG function in
the totals column in the query. When i look at the Query
Database, a message box appears saying "Overflow!". Im
not sure what to do because thats all the msg box says and
it does not advise me as what to do. Thanks for your
help! -Stu
 
G

Gary Walter

Stuart said:
I have 100 or so fields in a Query whose values come from
calculations in a couple other Querys. I Need the
averages for all these fields. I used the AVG function in
the totals column in the query. When i look at the Query
Database, a message box appears saying "Overflow!". Im
not sure what to do because thats all the msg box says and
it does not advise me as what to do. Thanks for your
help! -Stu
Click to expand...

Hi Stuart,

As a general rule, "Overflow" means you are probably
trying to divide by zero.

The first thing I might do is make a copy of your totals
query and change all the "Avg" to "Count."

If you get any results of Count = 0, I suspect you have
found the source(s) of your error.

So what do you do? I wish I had a simple answer but
a query based on other queries with 100 or so fields....

-One possibility... output a "Sum" and a "Count" on
each field, then in a report use calculation in a textbox
on your report to check for zero-count

=IIF([CountOfF1]=0, 0, [SumOfF1]/[CountOfF1])

-Another possibility, if you think you will have only one
or a few fields that you can identify as the only one(s) that
may have this problem,
use calculated expressions for those fields in your query,
and use "Avg" for the other fields.

Field: AvgF2: IIf(Count([f2])=0,0,Sum([f2])/Count([f2]))
Table:
Total: Expression

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
J

John F. Collins

Gary Walter said:
Hi Stuart,

As a general rule, "Overflow" means you are probably
trying to divide by zero.

The first thing I might do is make a copy of your totals
query and change all the "Avg" to "Count."

If you get any results of Count = 0, I suspect you have
found the source(s) of your error.
Click to expand...

I think that the problem is more complicated than that. If count gives 0,
then Avg should give an empty field.
So what do you do? I wish I had a simple answer but
a query based on other queries with 100 or so fields....

-One possibility... output a "Sum" and a "Count" on
each field, then in a report use calculation in a textbox
on your report to check for zero-count

=IIF([CountOfF1]=0, 0, [SumOfF1]/[CountOfF1])
Click to expand...

If Access is having to do this internally by keeping track of count and sum
as two different fields in the result befor computing average, then it is
posible that the 255 field limit is being exceeded internally. I find that
with 100 to 200 fields, I can calculate Count, and Average, but not StDev.
I have posted my problem earlier.
-Another possibility, if you think you will have only one
or a few fields that you can identify as the only one(s) that
may have this problem,
use calculated expressions for those fields in your query,
and use "Avg" for the other fields.

Field: AvgF2: IIf(Count([f2])=0,0,Sum([f2])/Count([f2]))
Table:
Total: Expression

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...

Here is one more off-the-wall solution. I have found that with some of the
queries that say "overflow" , if you go to SQL view, delete the blank line
at the end of the query, and switch straight to datsheet view, then the
query will run. There is something very shaky happening here.

John
 
Top