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 |
#11
|
|||
|
|||
Simple Query Question
Hi Chuck,
No need to explain. I kind of deserved it. I downloaded version 5.3 of this database and experimented with it. I also got the overflow message any time I attempted to add a criteria to the calculated field: AVE Batting: (Batting.H)/(Batting.AB) I found an older KB (Knowledge Base) article written for Access 97 that provides a solution: ACC97: Overflow or #Div/0! Error Occurs When You Run a Query, Form, or Report http://support.microsoft.com/?id=301672 They use a conditional IF (IIF) statement: =IIF([Divisor Field]=0,0,[Field A]/[Divisor Field]) but, they are not showing the use of a criteria on the [Divisor Field] to filter out zero values. While using IIF in a query can cause it to slow down, this appears to work for your query. Create a new query. Dismiss the add table dialog without adding any tables. In query design view, click on View SQL View. You should see the word SELECT highlighted. Copy the SQL (Structured Query Language) statement shown below, and paste it into the SQL window, replacing the existing SELECT that is highlighted. You can then return to normal query design view if you want, by clicking on View Design View. SELECT Master.playerID, Batting.yearID, Batting.teamID, Batting.AB, Batting.H, Pitching.W, Format(IIf(Batting.AB=0,0,Batting.H/Batting.AB),"0.000") AS [AVE Batting] FROM (Master INNER JOIN Batting ON Master.playerID = Batting.playerID) INNER JOIN Pitching ON Master.playerID = Pitching.playerID WHERE (((Batting.AB)0) AND ((Pitching.W)20) AND ((Format(IIf([Batting].[AB]=0,0,[Batting].[H]/[Batting].[AB]),"0.000"))=0.3)) ORDER BY Master.playerID, Batting.yearID; Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Chuck Hildebrandt" wrote: Tom: Thank you very much. Your response is very refreshing and unusual for Usenet, which at times can devolve into little better a schoolyard fight. I apologize for my own strong reaction -- I guess it just hit me a certain way this time. Not an excuse, just an explanation. On the actual issue at hand, the "overflow" issue -- it was half helpful. I was able to run the query successfully and to sort the resulting table by batting average, but I was not able to add a filtering criterion to a calculated field prior to running the query. I used the Lahman 5.3 table from www.baseball1.com. Here are the actual attributes I used: Master.playerID Batting.yearID Batting.teamID Batting.AB: 0 Batting.H Piching.W: =20 AVE: Batting.[H]/[AB] I ran this query and it executed successfully. But when I tried to filter the AVE attribute to return only those records of pitchers who also bating over .300 while winning 20 games, in this way: Master.playerID Batting.yearID Batting.teamID Batting.AB: 0 Batting.H Piching.W: =20 AVE: Batting.[H]/[AB]: =0.3 I got the Overflow message again. What's the difference here? By the way, I interrelated the playerID attribute among all three tables used (Master, Batting, Pitcing), and yearID between Batting and Pitching. Thanks again. Chuck |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Survey Results | SAm | Running & Setting Up Queries | 10 | May 17th, 2005 08:32 PM |
Simple Query Question - Access 2003 | [email protected] | General Discussion | 9 | January 14th, 2005 08:03 PM |
Simple Query Question | Rebecca | Running & Setting Up Queries | 1 | June 3rd, 2004 10:59 AM |