If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query OVERFLOW Error
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 |
#2
|
|||
|
|||
Query OVERFLOW Error
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. "John D" wrote: 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 |
#3
|
|||
|
|||
Query OVERFLOW Error
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" wrote in message ... 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 |
#4
|
|||
|
|||
Query OVERFLOW Error
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 = [Percen****er-TtlRev]) But when I then created a new query that did the .666 thing: SELECT [Percen****er-TtlRev].OrgID, [Percen****er-TtlRev].[Water %] FROM [Percen****er-TtlRev] WHERE (((Percen****er-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) |
#5
|
|||
|
|||
Query OVERFLOW Error
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 [Percen****er-TtlRev].[Water %] ; to rack and stack the values. If there is a lot of records, put in ORDER BY [Percen****er-TtlRev].[Water %] DESC ; to flip the records and see what shows up first that way. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "John D" wrote: 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 = [Percen****er-TtlRev]) But when I then created a new query that did the .666 thing: SELECT [Percen****er-TtlRev].OrgID, [Percen****er-TtlRev].[Water %] FROM [Percen****er-TtlRev] WHERE (((Percen****er-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) |
#6
|
|||
|
|||
Query OVERFLOW Error
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 |
#7
|
|||
|
|||
Query OVERFLOW Error
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 '================================================= === John D wrote: 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 |
Thread Tools | |
Display Modes | |
|
|