Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Top 7 of 9?

1. ## Top 7 of 9?

Hi!

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.

Best regards
Speedy

• Is it possible to get the sum of only the best 7 partx if the code looks like this?

Code:
```<%

Dim Conn, objrs, partCount
partCount = "part1+part2+part3+part4+part5+part6+part7+part8+part9"
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!"

else

do until objrs.EOF %>

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

<% objrs.movenext
loop
end if %>```
If not, is there any way to get the 7 best partx before adding them in the partCount?

/Speedy

•

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•