Zeb, I'll do what I can to help. I'm increasingly frustrated with Quickbooks and would love a good Rbase option to replace it with.
Could you show your complete select because I'm not sure I understand what you are trying to accomplish. Jan -----Original Message----- From: Zeb Zbranek <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Thu, 29 May 2014 07:06:45 -0700 (PDT) Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs Thank you. That saved many hours. Next question; I want to ultimately use my data to generate a bar chart showing the best years and one showing the best months over the years. In looking at R:Charts the bar graph requires a sequential field, so my next question is, how do I go from this: CaseYear|CaseCount 2010 | 13 1995 | 11 Not a pretty chart 2013 | 10 to: Row# | CaseYear|CaseCount 1 |2010 | 13 2 |1995 | 11 Pretty chart 3 |2013 | 10 I have taken the help you have given me and created a view. But for some reason when I insert the TOP 12 into the SELECT, the view only generates one row. I have been able to do this in two step, which I can live with, but the best help would be how to insert a row counter for my final view. Any ideas. I wrote the my original accounting program in 1988, updated it in 1994, and we have been using it ever since, unmodified. That was when it was R:Base 4.5+. Then came Windows 8.0 and my dos base program would not work. My choice was to buy something else and start over or convert to 9.5. I am a little rusty as programming is not my first calling. Although, I have found it intellectual challenging. My conversion is complete and my updated accounting program goes online, replacing my R:Base 4.5+, when our new compute comes in, in the next few days. Zeb Zbranek Zbranek Firm, P.C. 1937 Trinity Street Liberty, Texas 77575-2050 Telephone: 936-336-6454 Facsimile: 936-336-6039 The information contained in this message from Zbranek Firm, P.C. and any attachments are confidential and intended only for the named recipients. If you have received this message in error, you are prohibited from copying, distributing or using the information. Please contact the sender immediately by reply e-mail and delete the original message. On Wednesday, May 28, 2014 7:12 PM, Albert Berry <[email protected]> wrote: I think what you need to see is the COUNT(DISTINCT NameOfAthlete) which only counts each identical name as 1, no matter how many results there are. SELECT SeasonCCYY, COUNT(DISTINCT NameOfAthlete) + FROM IndividualResults + WHERE ETInSeconds > 0 + GROUP BY SeasonCCYY + ORDER BY 2 Desc + Albert On 5/28/2014 4:10 PM, Wills, Steve wrote: <!--{cke_protected}{C}%3C!%2D%2D%0A%23yiv6066471640%20%20%0A%20_filtered%20%23yiv6066471640%20%7Bfont-family%3ACalibri%3Bpanose-1%3A2%2015%205%202%202%202%204%203%202%204%3B%7D%0A%20_filtered%20%23yiv6066471640%20%7Bfont-family%3ATahoma%3Bpanose-1%3A2%2011%206%204%203%205%204%204%202%204%3B%7D%0A%23yiv6066471640%20%20%0A%23yiv6066471640%20p.yiv6066471640MsoNormal%2C%20%23yiv6066471640%20li.yiv6066471640MsoNormal%2C%20%23yiv6066471640%20div.yiv6066471640MsoNormal%0A%09%7Bmargin%3A0in%3Bmargin-bottom%3A.0001pt%3Bfont-size%3A12.0pt%3Bfont-family%3A%22Times%20New%20Roman%22%2C%20%22serif%22%3B%7D%0A%23yiv6066471640%20a%3Alink%2C%20%23yiv6066471640%20span.yiv6066471640MsoHyperlink%0A%09%7Bcolor%3Ablue%3Btext-decoration%3Aunderline%3B%7D%0A%23yiv6066471640%20a%3Avisited%2C%20%23yiv6066471640%20span.yiv6066471640MsoHyperlinkFollowed%0A%09%7Bcolor%3Apurple%3Btext-decoration%3Aunderline%3B%7D%0A%23yiv6066471640%20p.yiv6066471640MsoAcetate%2C%20%23yiv6066471640%20li.yiv6066471640MsoAcetate%2C%20%23yiv6066471640%20div.yiv6066471640MsoAcetate%0A%09%7Bmargin%3A0in%3Bmargin-bottom%3A.0001pt%3Bfont-size%3A8.0pt%3Bfont-family%3A%22Tahoma%22%2C%20%22sans-serif%22%3B%7D%0A%23yiv6066471640%20span.yiv6066471640EmailStyle17%0A%09%7Bfont-family%3A%22Calibri%22%2C%20%22sans-serif%22%3Bcolor%3A%231F497D%3B%7D%0A%23yiv6066471640%20span.yiv6066471640EmailStyle18%0A%09%7Bfont-family%3A%22Calibri%22%2C%20%22sans-serif%22%3Bcolor%3A%231F497D%3B%7D%0A%23yiv6066471640%20span.yiv6066471640BalloonTextChar%0A%09%7Bfont-family%3A%22Tahoma%22%2C%20%22sans-serif%22%3B%7D%0A%23yiv6066471640%20.yiv6066471640MsoChpDefault%0A%09%7Bfont-size%3A10.0pt%3B%7D%0A%20_filtered%20%23yiv6066471640%20%7Bmargin%3A1.0in%201.0in%201.0in%201.0in%3B%7D%0A%23yiv6066471640%20div.yiv6066471640WordSection1%0A%09%7B%7D%0A%2D%2D%3E--> Zeb, I probably should have simplified this; I just took a previously existing query and tested it, then pasted it, but, related to what I think your need is, the following might have been better: SELECT SeasonCCYY, COUNT(NameOfAthlete) FROM INDIVIDUAL_RESULTS WHERE ETInSeconds <> 0 GROUP BY SeasonCCYY ORDER BY 2 DESC Translation: Get the year, total number of runners from individual performance records where the runner crossed the finish line grouped by the year ordered by how many finishers during the season from most to least From:[email protected] [mailto:[email protected]] [mailto:[email protected] [mailto:[email protected]]] On Behalf OfWills, Steve Sent: May 28, 2014 4:38 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs Zeb (and Bill), what about something like this: SELECT SeasonCCYY, NameOfSchoolCD, COUNT(NameOfAthlete) FROM INDIVIDUAL_RESULTS WHERE ETInSeconds <> 0 AND NameOfSchoolCD IN ('SAA','SDS') GROUP BY SeasonCCYY, NameOfSchoolCD, ORDER BY 2, 3 DESC Translation: Get the season, name of the school how many runner names from individual results table where performance/elapsed time has a positive integer value and name of the school is for my girls and my boys teams group by the season, name of the school order by name of the school (to keep boys and girls counts together) how many runner names from most to least (IOW, which year’s team has had the most runners) From:[email protected] [mailto:[email protected]] [mailto:[email protected] [mailto:[email protected]]] On Behalf OfBill Downall Sent: May 28, 2014 4:23 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs On Wed, May 28, 2014 at 5:18 PM, A. Razzak Memon <[email protected] [mailto:[email protected]]> wrote: It currently takes me two steps to answer this question: (1) SELECT MAX(CaseCount) FROM GroupCaseCountByYear; (2) SELECT CaseYear WHERE CaseCount = #. (#--is the number I got from step (1)) This seems counter productive. Zeb, You can combine your two queries into one: SELECT CaseYear FROM GroupCaseCountByYear + WHERE CaseCount = (SELECT MAX (CaseCount) FROM GroupCaseCountByYear)
