Query OVERFLOW Error

[John D]

I've tried as best I can to follow suggestions in this forum about "fixing"
the Overflow error I'm getting in my query, but so far no good.

I have a table of financial statement values - 1 record per value. Three of
the fields in the table are [OrgID], [Activity Code], and [Value]. [Value] is
Numeric - Single data type.

[OrgID] identifies nonprofit organizations. [Activity] is a code, 1 through
54, that defines different services that can be provided by those nonprofit
organizations. A nonprofit can provide any number of services, but there is
only 1 [Value] associated with each [OrgID] - [Activity] combination.

I based a Crosstab Query [CT_RevAll] on this table where [OrgID] are the
rows, [Activity] are the columns, and [Value] are the "cells". I have a
selection criteria that limits [Value] to Total Revenue gained from each
service.

I'm building a Select Query that is attempting to select all [OrgID]'s from
the Crosstab Query where more than two-thirds of that nonprofit's revenue is
derived from Activity 54. When I run that query I get the "Overflow" message.

After several experiments, this is the "simple" expression of the Query's SQL:

SELECT CT_RevAll.OrgID,
CT_RevAll.[Value],
CT_RevAll.[54],
Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0) AS [Water %]

FROM CT_RevAll

WHERE
(
((CT_RevAll.[Value]) Is Not Null And (CT_RevAll.[Value])>0)

AND
((CT_RevAll.[54]) Is Not Null And (CT_RevAll.[54])>0))

AND
((Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))>0.666)


If I take the last "AND" clause out, the query runs. With it in, I get
"Overflow".

I realize I probably don't need the Nz in the calculation expression in the
SELECT clause because I'm converting both numerator and denominator Non Null
values to Zero in the WHERE clause. Also, I'm selecting only numerator and
denominator values that are >0, which prevents the division by 0 problem (I
think).

Anyone see what's the problem? Thanks - John D

 

[Jerry Whittle]

While I haven't seen this in Access, some other databases are very picky
about the order of the WHERE clause. They often start at the bottom and work
their way up. In your case that could cause a divide by zero error. Try
moving the last line up to the top of the WHERE clause.

To make matters worse, Access might be optimizing the SQL statement and
putting that part of the WHERE clause where ever it wants like moving it to
the bottom again! This happened to me when moving from Oracle 8i to 10g. In
cases like that you have to write a subquery to gather up all the records
where not null or >0 then act on that record set. You could try saving the
query that works then creating a second query based on it to get the rest of
the information.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I've tried as best I can to follow suggestions in this forum about "fixing"
the Overflow error I'm getting in my query, but so far no good.

I have a table of financial statement values - 1 record per value. Three of
the fields in the table are [OrgID], [Activity Code], and [Value]. [Value] is
Numeric - Single data type.

[OrgID] identifies nonprofit organizations. [Activity] is a code, 1 through
54, that defines different services that can be provided by those nonprofit
organizations. A nonprofit can provide any number of services, but there is
only 1 [Value] associated with each [OrgID] - [Activity] combination.

I based a Crosstab Query [CT_RevAll] on this table where [OrgID] are the
rows, [Activity] are the columns, and [Value] are the "cells". I have a
selection criteria that limits [Value] to Total Revenue gained from each
service.

I'm building a Select Query that is attempting to select all [OrgID]'s from
the Crosstab Query where more than two-thirds of that nonprofit's revenue is
derived from Activity 54. When I run that query I get the "Overflow" message.

After several experiments, this is the "simple" expression of the Query's SQL:

SELECT CT_RevAll.OrgID,
CT_RevAll.[Value],
CT_RevAll.[54],
Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0) AS [Water %]

FROM CT_RevAll

WHERE
(
((CT_RevAll.[Value]) Is Not Null And (CT_RevAll.[Value])>0)

AND
((CT_RevAll.[54]) Is Not Null And (CT_RevAll.[54])>0))

AND
((Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))>0.666)


If I take the last "AND" clause out, the query runs. With it in, I get
"Overflow".

I realize I probably don't need the Nz in the calculation expression in the
SELECT clause because I'm converting both numerator and denominator Non Null
values to Zero in the WHERE clause. Also, I'm selecting only numerator and
denominator values that are >0, which prevents the division by 0 problem (I
think).

Anyone see what's the problem? Thanks - John D

 

[John Spencer]

The overflow error is caused by you dividing by zero. Cannot do that.

Try something like the following
IIF(Nz([CT_RevAll]![Value],0)=0,0,Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))
AS [Water %]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John D]

Jerry - wow! Is that one of those wonderful "undocumented features"?

I tried your suggestion of removing the:

((Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))>0.666)

line from the query - and as before the query works. (Query name =
[PercentWater-TtlRev]) But when I then created a new query that did the >.666
thing:

SELECT [PercentWater-TtlRev].OrgID, [PercentWater-TtlRev].[Water %]
FROM [PercentWater-TtlRev]
WHERE (((PercentWater-TtlRev].[Water %])>0.666));

: ( - didn't work. "Overflow".
This would have avoided the undocumented feature of moving lines of the
WHERE clause around - right? (Thanks)

 

[Jerry Whittle]

On your second query, what kind of values are you getting for Water % if you
take out the WHERE clause?

You might want to put in an
ORDER BY [PercentWater-TtlRev].[Water %] ;

to rack and stack the values. If there is a lot of records, put in
ORDER BY [PercentWater-TtlRev].[Water %] DESC ;

to flip the records and see what shows up first that way.

 

[John D]

Well guys - I was experimenting with both your suggestions, when I decided to
start over as simply as possible. And I'll be darned - guess what works?:

SELECT
CT_RevAll.OrgID,
CT_RevAll.[54],
CT_RevAll.[Value],
CT_RevAll.[54]/CT_RevAll.[Value] AS [Water %]

FROM QAnal_CT_RevAll

WHERE
(((CT_RevAll.[54]) Is Not Null And (CT_RevAll.[54])>0)
AND ((CT_RevAll.[Value]) Is Not Null And (CT_RevAll.[Value])>0)
AND ((CT_RevAll.[54]/CT_RevAll.[Total Of 2005])>0.666));

The only difference from my original SQL posting is that I didn't do the
"Nz..." thing in the 4th line of the SELECT clause - in the original:

Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0) AS [Water %]

My original thought that the "Not Null" and Nz... elements of the WHERE
clause tests of the 2 data fields would prevent the dividing by 0 problem.
When I dropped the "Nz..." thing in the SELECT clause, they appear to do so.

Now - although I'm happy the query is working, Why the heck does the Nz
thing in the SELECT clause lead to an Overflow? It just seems like a
"redundant test" to me since the WHERE clause already excludes all "0" and
"Null" values from both numerator and denominator.

Thanks for your help - and do you see what gives? Thanks - John D

 

[John Spencer]

But when you do the following in the WHERE clause it will be run against
every record. The engine does not "know" that row will be eliminated
based on the other criteria and therefore it doesn't "know" that it no
longer needs to run this test.

((Nz([CT_RevAll]![54],0)/Nz([CT_RevAll]![Value],0))>0.666)

When the calculation is done in the SELECT clause the records with null
or zero values have already been eliminated since the criteria have
already been applied to the record.

***M Y*** understanding is that the query process runs in this order
(generally)

-- do the joins
-- do the WHERE (select the records)
-- Order the records (if specified)
-- Return the fields in the select clause

(of course that leaves out aggregation if used and subqueries, but that
is the general flow as I understand it.)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================