 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2006Simple Query Question
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, 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. [quoted text clipped - 14 lines] > > Chuck http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public .access.queries&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, > [quoted text clipped - 31 lines] > > > > Chuck What the hell are you talking about? I posted it here at 1059pm. > Chuck, > [quoted text clipped - 34 lines] >> >> Chuck 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/community/en-us/default.mspx?dg=microsoft.public .access.queries&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. 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" <mcm...@enteract.com> 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: <VGvMf.16915$NS6.12455@newssvr30.news.prodigy.com> NNTP-Posting-Host: 67.37.176.215 X-Complaints-To: abuse@prodigy.net 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==?=" <pskwaak@discussions.microsoft.com> Subject: Overflow Message Date: Sat, 25 Feb 2006 16:14:26 -0800 Lines: 2 Message-ID: <5762BF23-5976-4D3F-BA77-1F006807CDC6@microsoft.com> 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 [quoted text clipped - 29 lines] > >> What the hell are you talking about? I posted it here at 1059pm. 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> 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 __________________________________________ > Complete missing paragraph: > [quoted text clipped - 4 lines] > > <snip> 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 > Chuck, > [quoted text clipped - 18 lines] >> >> <snip> 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: > [quoted text clipped - 41 lines] > > Chuck > Chuck, > [quoted text clipped - 5 lines] > 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)  SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.