View Full Version : Top 7 of 9?

02-24-2007, 10:11 PM

I'm trying to make database (access) driven "scoreboard" where there is 9 part competitions that leads to a final at the end of the season.

First of all I wonder if it's best to set up a table for each part competition or if it's best to set up a table for each year with a column for each part competition?

If the answer is a table per part competition I guess that I need to put a column called year to be able to go back and look at old scores in the future?

Each player that participates get points depending on how they end up in the competition. This year there is 9 part competitions but only the 7 best competitions for each player will be counted.

My second question is how the SELECT should look like to count only the top 7 competitions?

Let's call the columns or tables (depending on the answer in question one) part1, part2, part3 and so on.

Any advice/answer is apriciated
Best regards

02-25-2007, 06:35 PM
Is it possible to get the sum of only the best 7 partx if the code looks like this?


Dim Conn, objrs, partCount
partCount = "part1+part2+part3+part4+part5+part6+part7+part8+part9"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../sys/top71.mdb")
Set objrs = Conn.Execute("SELECT sum("&partCount&") as Total FROM points WHERE sportid = 'M211175STE02'")

If objrs.EOF then

Response.Write "Something went wrong!"


do until objrs.EOF %>

<%=objrs("total") %> <br />

<% objrs.movenext
end if %>

If not, is there any way to get the 7 best partx before adding them in the partCount?