Simple Query Question

[Chuck Hildebrandt]

I want to query for all MLB pitchers who won 20 games and batted at least
..300 in the same season.

I successfully linked my Pitchers and Batters table and inserted the
calculated field for batting average ([H]/[AB]), ran the query, and it
worked. So then , so far so good.

However, when I went back and tried to limit the records returned on the
query to >=.3 on batting averages, or even when I tried to sort the query
results by batting average in the datasheet view, I got the dialog box that
says "Overflow".

Why does this happen? What is the purpose of it? How do I get around it
short of copying my query results into Excel and sorting it manually there?

Thanks.

Chuck

 

[Guest]

Chuck,

You posted this question in the Queries newsgroup at 4:13 PM PST. You
received answers from two Microsoft Access MVP's. You really should continue
with that thread, rather than cross post. Cross posting is considered rude.
It's okay to multipost, when appropriate, but cross posting should be
avoided. If you absolutely must cross post, you should clearly indicate in
the post that you already asked your question in another newsgroup, and it
went unanswered.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

http://www.microsoft.com/office/com...ries&mid=6f31e90d-d9f5-4daa-ab70-8e65edf1c9d2


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Chuck,

You posted this question in the Queries newsgroup at 4:13 PM PST. You
received answers from two Microsoft Access MVP's. You really should continue
with that thread, rather than cross post. Cross posting is considered rude.
It's okay to multipost, when appropriate, but cross posting should be
avoided. If you absolutely must cross post, you should clearly indicate in
the post that you already asked your question in another newsgroup, and it
went unanswered.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I want to query for all MLB pitchers who won 20 games and batted at least
..300 in the same season.

I successfully linked my Pitchers and Batters table and inserted the
calculated field for batting average ([H]/[AB]), ran the query, and it
worked. So then , so far so good.

However, when I went back and tried to limit the records returned on the
query to >=.3 on batting averages, or even when I tried to sort the query
results by batting average in the datasheet view, I got the dialog box that
says "Overflow".

Why does this happen? What is the purpose of it? How do I get around it
short of copying my query results into Excel and sorting it manually there?

Thanks.

Chuck

 

[Chuck Hildebrandt]

What the hell are you talking about? I posted it here at 1059pm.

Chuck,

You posted this question in the Queries newsgroup at 4:13 PM PST. You
received answers from two Microsoft Access MVP's. You really should
continue
with that thread, rather than cross post. Cross posting is considered
rude.
It's okay to multipost, when appropriate, but cross posting should be
avoided. If you absolutely must cross post, you should clearly indicate in
the post that you already asked your question in another newsgroup, and it
went unanswered.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I want to query for all MLB pitchers who won 20 games and batted at least
..300 in the same season.

I successfully linked my Pitchers and Batters table and inserted the
calculated field for batting average ([H]/[AB]), ran the query, and it
worked. So then , so far so good.

However, when I went back and tried to limit the records returned on the
query to >=.3 on batting averages, or even when I tried to sort the query
results by batting average in the datasheet view, I got the dialog box
that
says "Overflow".

Why does this happen? What is the purpose of it? How do I get around it
short of copying my query results into Excel and sorting it manually
there?

Thanks.

Chuck

 

[Rick Brandt]

Chuck,

You posted this question in the Queries newsgroup at 4:13 PM PST. You
received answers from two Microsoft Access MVP's. You really should continue
with that thread, rather than cross post. Cross posting is considered rude.
It's okay to multipost, when appropriate, but cross posting should be
avoided. If you absolutely must cross post, you should clearly indicate in
the post that you already asked your question in another newsgroup, and it
went unanswered.

I agree completely except that you've got the terminology backwards.

Limited Crossposting is OK
(one message simultaneously posted to multiple groups)

Multi-Posting Is NOT OK
(separate posts of the same content to multiple groups)

 

[Guest]

Then I suspect that your system clock is off (slow) by about 5 minutes. You
said you posted here at 10:59pm. Not knowing what time zone you are in, but
comparing this quoted time with the time of your initial post as displayed to
me (9:06 PM PST), I can only guess that 9:06 PM corresponds to 11:06 PM your
time. You do know what PST stands for don't you? It's Pacific Standard Time.

So, you are saying that another person who goes by the sign-in name of
"pskwaak" must have posted a very similar question dealing with baseball
statistics, with a similar Overflow error message, and even used some of the
same abbreviations that you used in this thread (ie. [h] and [ab] in square
brackets)? I find that kind of hard to believe. In any case, go read the
answers provided by Ken Snell and Doug Steele. The same answer is valid for
your question (since it's basically the same question!). Here's that link one
more time:

http://www.microsoft.com/office/com...ries&mid=6f31e90d-d9f5-4daa-ab70-8e65edf1c9d2



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Chuck Hildebrandt]

Oh, LOL! NOW I see what you're talking about!

Why, yes, Tom, it IS possible that two completely different people might
post a very similar question about the very same topic, using a similar
example, to two different newsgroups, in the same weekend. That's exactly
what happened here.

It may be it is kind of hard for you to believe. Maybe even impossible.
And perhaps you also find it kind of hard to believe that the second and the
third presidents of the United States both died of natural causes hundreds
of miles away from each other on the very same day, which happened to be the
50th anniversary of the Declaration of Independence, to which they were both
signatory.

Try to think about this logically: why would I bother cross-posting the same
question a second time, in a more verbose, more

You appear to like to play cross-posting cop here -- I see another "bust"
you made of someone else in the same window view. But you also seem to be a
smart guy ... smart enough to check the message sources on postings by two
posters under different names before you make cross-posting accusations on
flimsy circumstantial evidence.

Look at mine:

From: "Chuck Hildebrandt" <[email protected]>
Newsgroups: microsoft.public.access.gettingstarted
Subject: Simple Query Question
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <[email protected]>
NNTP-Posting-Host: 67.37.176.215
X-Complaints-To: (e-mail address removed)
X-Trace: newssvr30.news.prodigy.com 1141016373 ST000 67.37.176.215 (Sun, 26
Feb 2006 23:59:33 EST)
NNTP-Posting-Date: Sun, 26 Feb 2006 23:59:33 EST
Organization: SBC http://yahoo.sbc.com
X-UserInfo1:
OPYCRTKE^ZCQW^I]^ROZOTPARBZZTB\MV@BNMRQIMASJETAANVW[AKWZE\]^XQWIGNE_[EBL@^_\^JOCQ^RSNVLGTFTKHTXHHP[NB\_C@\SD@EP_[KCXX__AGDDEKGFNB\ZOKLRNCY_CGG[RHT_UN@C_BSY\G__IJIX_PLSA[CCFAULEY\FL\VLGANTQQ]FN
Date: Mon, 27 Feb 2006 04:59:33 GMT


Look at the other guy's:

Thread-Topic: Overflow Message
thread-index: AcY6aZqmEvQKso/cTJWad5m9gDMjWw==
X-WBNR-Posting-Host: 68.226.60.34
From: "=?Utf-8?B?cHNrd2Fhaw==?=" <[email protected]>
Subject: Overflow Message
Date: Sat, 25 Feb 2006 16:14:26 -0800
Lines: 2
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.queries
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path:
newsdbm06.news.prodigy.com!newsdst02.news.prodigy.com!newsmst01b.news.prodigy.com!prodigy.com!newscon06.news.prodigy.com!prodigy.net!newshub.sdsu.edu!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl


Not really so close, now, are they?

By the way, regarding the time issue you felt compelled to point out? Note
the posting time on my message: one part says "Sun, 26 Feb 2006 23:59:33
EST", and the other says "Mon, 27 Feb 2006 04:59:33 GMT". Note the part
that says "59". I don't know if your clock is off (fast) by about five
minutes, but that's what the message source says. You can query the message
on Google Groups if you care to verify the time. You DO know what "EST"
and "GMT" mean, don't you? They're Eastern Standard Time and Greenwich Mean
Time. And just to head off any confusion you might have, "EST" is short for
Eastern Standard Time, and "GMT" is short for Greenwich Mean Time -- not the
other way around.

Jackass.

Then I suspect that your system clock is off (slow) by about 5 minutes.
You
said you posted here at 10:59pm. Not knowing what time zone you are in,
but
comparing this quoted time with the time of your initial post as displayed
to
me (9:06 PM PST), I can only guess that 9:06 PM corresponds to 11:06 PM
your
time. You do know what PST stands for don't you? It's Pacific Standard
Time.

So, you are saying that another person who goes by the sign-in name of
"pskwaak" must have posted a very similar question dealing with baseball
statistics, with a similar Overflow error message, and even used some of
the
same abbreviations that you used in this thread (ie. [h] and [ab] in
square
brackets)? I find that kind of hard to believe. In any case, go read the
answers provided by Ken Snell and Doug Steele. The same answer is valid
for
your question (since it's basically the same question!). Here's that link
one
more time:

http://www.microsoft.com/office/com...ries&mid=6f31e90d-d9f5-4daa-ab70-8e65edf1c9d2



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

What the hell are you talking about? I posted it here at 1059pm.

 

[Chuck Hildebrandt]

Complete missing paragraph:

Try to think about this logically: why would I bother cross-posting the same
question a second time, in a more verbose manner (ten times as many lines),
rather than simply copying the question from the first post and pasting it
in for the second post?


<snip>

 

[Guest]

Chuck,

I was wrong. Sorry. Really.
Were the answers that the other person received helpful for you?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Chuck Hildebrandt]

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

 

[Guest]

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
__________________________________________

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