
Still having some questions about statistics
I am getting there, but have a problem i can't resolve yet.<BR>My database looks like this:<BR>table colums contain: Player, course, round, score, and a number of details for that round, a total of 25 columns<BR>then each row contains a new round for each player.<BR>There are 15 Tours of 2 rounds, and 4 Tours of 4 rounds, we have 25 players, so i'll probably end up with 1100 records :)<BR>My problem is this: I want to display the 2 (or 4) rounds of each Tour in one sheet, AND want to rank the players at the same time by avg score, but the players that only played 1 round have to be ranked lower than the players that played 2 rounds, it gets even trickier when a Tour has 4 rounds.... any1 having an idea?

If you end up with...
...1100 records, then several people didn't play all their rounds.<BR><BR>My math says<BR>( (15 x 2) + (4 x 4) ) x 25 <BR>is <BR>( 30 + 16 ) x 25<BR>is<BR>46 x 25<BR>is <BR>1150<BR><BR>[I did it by saying "25 is a quarter of 100, 46 x 100 is 4600, a quarter of 4600 is half of half of 4600, or half of 2300, which is 1150." Your mileage may vary.]<BR><BR>ANYWAY...<BR><BR>Let's see if we can do this all in one query...<BR><BR>First question: How many points are possible in one round per player? Let me assume the number is less that a million. If so:<BR><BR>SELECT ( Count(*) * 1000000.0 ) + Avg(score) AS Rank,<BR> Avg(score) AS PlayerAvg, <BR> Player<BR>FROM Rounds<BR>WHERE tourNumber = 3 <BR>GROUP BY Player<BR>ORDER BY 0 Desc<BR><BR>That doesn't show the scores for the individual rounds. It only gets the rankings and averages for all players for one Tour. [You didn't mention a "tourNumber" field in your posting, but I assume it must be there?]<BR><BR>See the trick in generating the rankings? A person who only plays 1 round will have Count(*) of 1, so his rank will be 1000000 plus his average. Somebody who has played two rounds will have a rank of 2000000 plus his average. Etc. So all those who finished all rounds will always be ranked ahead of those who haven't.<BR><BR>NOW...<BR><BR>*IF* you *also* need all the perround details on a perplayer basis in the same report, that gets trickier. I haven't figured out a way to do that without a temp table, yet. Though a temp table isn't a horrible solution, I suppose. Okay...with a temp table:<BR><BR>Just change the above query so it does "SELECT ... INTO TEMP" and then do<BR><BR>SELECT * FROM Rounds, Temp<BR>WHERE Rounds.Player = Temp.Player<BR>AND Rounds.tourNumber = 3<BR>ORDER BY Temp.Rank, Rounds.round<BR><BR>Untested, but it looks to me like it would work.<BR><BR>

RE: If you end up with...
yes you are right, i have a field with "tournumber", actually it is the name of the course that is played. And yes, not all members play their rounds. I had a lil trouble with the ranking because the lower the avg, the better the rank, but I solved it like this:<BR>SELECT (COUNT(*) * 10000.0) + AVG(H18) AS Rank, <BR>AVG(H18) AS Average,name <BR>FROM rounds WHERE H18 > 0 AND course = 'Kapalua Plantation 2000' GROUP BY name <BR>ORDER BY (COUNT(*) * 10000.0) + AVG(H18)<BR><BR>the ORDER BY 0 you suggested doesn't work on our server, it seems invalid<BR><BR>by the way scores are ranged between 55 and 90, lowest score is best. (GolfTourney)<BR><BR>Anyway, thank you very much for your suggestion, I can go on with my sheets again :))<BR>THANKS JAN <BR> <BR>

Sorry about the typo!
Should have been <BR> ORDER BY 1<BR><BR>The SELECTed fields are numbered, starting at 1, in SQL. So that ORDER BY 1 is equivalent to the ORDER BY you actually used.<BR><BR>RecordSet fields are, of course, numbered from zero and I just slipped a mental cog for a moment there.<BR><BR>I like your solution for the ranking!<BR><BR>I probably would have done something like:<BR><BR>( ( 10  COUNT(*) ) * 10000 ) + AVG(H18) <BR><BR>but yours works as well and is shorter.<BR><BR>How come you need the "WHERE H18 > 0" in that query??? Why would you put in a record, at all, if there is no score?<BR><BR>Ummm...just realized...H18 is probably "Handicapped 18 hole score", yes? And you probably also have H9 or some such? <BR><BR>Anyway, cute solution, I hope you agree! Sometimes math works where complicated logic doesn't.<BR><BR>

RE: Sorry about the typo!
You guessed right again :) The members have the possibility to play only 9 holes at once and submit their score and stats, then play the other 9 holes later. To avoid getting those scores in the rankings, i leave the H18 field valued "zero" untill the entire 18 holes are played, and half finished rounds will not be displayed by stating "WHERE H18 > 0"<BR><BR>In the *ReportForm* I also use the input to avoid double posts by this code:<BR><%<BR>'check if a part or the whole round already has been played<BR><BR>set Grs=Server.CreateObject("adodb.Connection")<BR>Gst rDS = "DRIVER={Microsoft Access Driver (*.mdb)};"&"DBQ=" &"\BBzServerpga.mdb"&";"<BR>Grs.open(GstrDs)<BR>Gs qlstmt = "SELECT * from rounds WHERE (name= '"& sName &"' AND course= '"& course &"' AND round= '"& bround &"')"<BR>set GTs = Grs.execute(Gsqlstmt)<BR><BR>If GTs.eof then<BR> rnds= 0<BR> rndsF9= 0<BR> rndsB9= 0<BR> rnds18= 0<BR>Else<BR> F9Score = GTs("F9")<BR> If F9Score > 0 THEN<BR> rndsF9 = 1<BR> End If<BR> B9Score = GTs("B9")<BR> If B9Score > 0 THEN<BR> rndsB9 = 2<BR> End If<BR> Score18 = GTs("H18")<BR> If Score18 > 0 THEN<BR> rnds18 = 6<BR> End If<BR>End If<BR><BR>rnds= rndsF9 + rndsB9 + rnds18<BR><BR>GTs.close<BR>set GTs=nothing<BR>%><BR><BR><BR><BR> <table><BR> <tr><BR> <td>Enter Score</td><BR> </tr><BR> <tr><BR> <td>Front 9</td><BR> <td><BR> <%<BR> If rndsF9 = 1 Then<BR> response.write"You posted a Front 9 score of "& F9Score &" already"<BR> response.write"<input type='HIDDEN' name='F9Score' value="& F9Score &">"<BR> ElseIf rnds > 5 Then<BR> response.write" "<BR> response.write"<input type='HIDDEN' name='F9Score' value="& F9Score &">"<BR> Else<BR> %><BR> Â* <select name="F9Score"><BR> <option selected value=0>Front 9 Score</option><BR><% <BR>For x=20 to 63<BR> response.write "<option value=" & x & ">" & x & "</option>"<BR>next<BR>%><BR> </select><BR><% End if %><BR><BR> </td><BR> </tr><BR> <tr><BR> <td>Or Back 9</td><BR> <td><BR> <%<BR> If rndsB9 = 2 Then<BR> response.write"You posted a Back 9 score of "& B9Score &" already"<BR> response.write"<input type='HIDDEN' name='B9Score' value="& B9Score &">"<BR> ElseIf rnds > 5 Then<BR> response.write" "<BR> response.write"<input type='HIDDEN' name='B9Score' value="& B9Score &">"<BR> Else<BR> %><BR> <select name="B9Score"><BR> <option selected value=0>Back 9 Score</option><BR><% <BR>For x=20 to 63<BR> response.write "<option value=" & x & ">" & x & "</option>"<BR>next<BR>%><BR> </select><BR> <% End if %><BR><BR> </td><BR> </tr><BR> <tr><BR> <td>Or 18 holes</td><BR> <td><BR> <%<BR> If (rndsF9 = 1) OR (rndsB9) = 2 Then<BR> response.write" "<BR> response.write"<input type='HIDDEN' name='Score18' value="& Score18 &">"<BR> ElseIf rnds > 5 Then<BR> response.write"You already played this round, if there was a mistake, contact Dz or ThunderHawk"<BR> response.write"<input type='HIDDEN' name='Score18' value="& Score18 &">"<BR> Else<BR> %><BR> <select name="Score18"><BR> <option selected value=0>18 Holes Score</option><BR><% <BR>For x=50 to 99<BR> response.write "<option value=" & x & ">" & x & "</option>"<BR>next<BR>%><BR> </select><BR> <% End if %><BR> </td><BR> </tr><BR> <tr><BR> <td>Â* <input type="submit" value="Send"><BR> <input type="reset" value="Reset" onclick=history.back()></td><BR> </tr><BR> </table><BR><BR><BR> <input type="HIDDEN" name="sName" value="<%= sName %>"><BR> <input type="HIDDEN" name="course" value="<%= course %>"><BR> <input type="HIDDEN" name="bround" value="<%= bround %>"><BR> <input type="HIDDEN" name="cPar" value="<%= cPar %>"> <BR></form>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

