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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Chuck Hildebrandt said:
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
